Arrays in Excel
Airfare Daily Deals eCigarettes Eyeglasses Hotels Jewelry Online Backup Online Dating Online Printing Online Tickets Skin Care Textbook Rentals Vitamins Web Hosting Weddings
Find thousands of shopping-related forums
SEARCH

Arrays in Excel

Arrays In Excel A worksheet is a collection of related data. Thus, you have to work with more than one cell at time. You need to refer to entire rows and columns or a group of cells in order to perform calculations and data analysis. Arrays and named ranges are two features in Excel that allow you to work with a group of cells at a time by performing an operation on the entire range.

Arrays  In Excel

A worksheet is a collection of related data. Thus, you have to work with more than one cell at time. You need to refer to entire rows and columns or a group of cells in order to perform calculations and data analysis. Arrays and named ranges are two features in Excel that allow you to work with a group of cells at a time by performing an operation on the entire range.

Arrays

Arrays are rectangular ranges of formulas or values that Excel treats as a single group. Arrays provide us with an efficient method of performing a large amount of calculation in a few steps. For example, an array of formulas is treated as a single formula even though it affects many cells. This occupies less memory and also helps in maintaining data better.

Assume that you have a set of products with their price and quantity entered in a worksheet. You need to calculate the total value by multiplying the price per unit work with the quantity.

Using standard techniques, you can find the value by entering the following formula in the cell E3:

=C3*D3

Then you need to copy the formula down to all the other cells in the E column. This method requires a formula for each cell that produces a result.

An efficient alternative to this method is by using arrays. You can enter a single array formula in cell F5 that fills the range from E3 through E7 and still uses the memory required from a single formula.

Entering Array Formulas

Arrays represent a group of cells where the first cell in the range is separated from the last cell with a colon (:). Thus, E3 to E7 will be written as E3 :E7.

The steps to enter an array formula are:

? Select the range that should contain the array formula. In this example, it is E3 to E7.

a With the range selected, enter the formula in the list cell of the range that should store the result.

In this case, enter the following formula in E3: =C3:C7*D3:D7

Press the Shift + Ctrl + Enter keys to enter the formula as an array.

The product of price and quantity is calculated and stored in the value column.

A formula referring to an array of cells is evaluated differently from a simple formula. In this case, rather than multiplying two cells, the formula multiplies the two arrays C3 :C7 and D3 :D7 by taking each corresponding element from the two arrays and multiplying them in pairs.

 

Related keywords: nuance vs adobe acrobat
Need an answer?
Get insightful answers from community-recommended
experts
in Software on Knoji.
Would you recommend this author as an expert in Software?
You have 0 recommendations remaining to grant today.
Comments (0)
ARTICLE DETAILS
RELATED ARTICLES
ARTICLE KEYWORDS