MS Office Contents
Home
| Page Contents |
| Array Constants | Entering Array Constants | Editing Arrays |
More About Arrays from Excel Help |
|
MS Office Contents
|
Home
|
| Arrays are formulas that can produce more than
one result.
"…A single-value formula produces a single result from one or more …values. For example, the formula =A1+B1…" totals the values in cells A1 and B1. " However, the array formula {=A1:A3+B1:B3}* produces a set of three results—the sum of the values in cells A1 and B1, the sum of the values in cells A2 and B2, and the sum of the values in cells A3 and B3." * (from the Microsoft Excel User's Guide, page 171). *An array formula is recognizable by the braces { } that enclose it. However, Excel won't recognize an array formula if you type the braces yourself. Enter the formula as you normally would, then hold down [Ctrl + Shift] while you press [Enter]. Excel will insert the braces for you. Using array formulas can simplify some of your calculations by allowing you to replace several formulas with a single array formula that consolidates all of the calculations into a single result. The example below, from page 172 of the Microsoft Excel User's Guide, demonstrates the differences. The cells on the left use a separate formula in each of cells B1 through B5 to calculate the square roots of the numbers in column B. Each function must be entered separately (or you can copy the square root function from B1 into B2-B5, then change the cell references to A2, A3, A4, and A5; all of the copies will reference A1 unless/until you change them). In the right half of the table below, the range B1:B5 was selected and the formula typed into the first cell and entered using [Ctrl + Shift + Enter]. The identical formula in each cell produces the correct result, in order, for each cell in the referenced range. The next table demonstrates that the results obtained from the two methods are identical. In the first table, 5 separate formulas were entered. In the second table, a single formula was entered.
The savings in time and effort might be insignificant if you are only performing a calculation on 4 or 5 elements. However, arrays can contain as many as 6500 elements. In large spreadsheets, they can really be convenient. Consider using array formulas when you need to total 200 columns across a spreadsheet or multiply a few dozen dollar amounts by, for example, a fixed interest rate. Try this to see how it works: we'll add 10% to each number in a large column of numbers. First, create the column of numbers using AutoFill: type a 1 in the first cell and a 2 in the cell beneath it. Highlight both cells, then click on the copy handle (the little dark square at the lower right of the active cell outline) and drag down through 25 or 30 rows. When you release the mouse button, the cells will be filled with sequential numbers 1-xx.
The asterisk (*) is the multiplication symbol for calculations on your PC. In the first part of the formula, we multiplied each element, F1-F35, by 10% then added the calculated amount to the original number. See Order of Operations in Formulas for an explanation of the use of parentheses and for a list of other math operators (+, -, /, etc.).
The above tables demonstrate how you can simplify a worksheet by not performing unnecessary calculations where seeing intermediate results is unnecessary. The table on the left first calculates the values of columns B, C, and D in three separate formulas in B4, C4, and D4 and then totals them in B6. The table on the right uses an array formula that calculates the value for each column, then sums the three totals in a single formula. (example from Excel User's Guide, page 173; also see Excel Help – search for array formulas).
Array ConstantsArray constants can be used in any array formula where you use a reference to a range of cells. Use array constants instead of references when you don't want to enter each constant value in a separate cell in a worksheet. In the table on the left below, cells C2-C5 contain an array formula, {=A2:A5*B2:B5}, that calculates the Price/Unit amount by multiplying the length in feet by the price per foot. The Price/Foot values have each been entered manually in column B, and the calculations were made by multiplying the contents of a cell in column A by the price in column B.
The table on the right uses array constants; in this table it wasn't necessary to display the Price/Foot so the prices were not entered in a separate column. They were entered into the array formula instead. Cells B2-B5 in this table contain the array formula {=A2:A5*{4;3.75;3.25;2.50}}
Entering Array Constants:
For example, to use the 2-by-3 (2 rows by 3 columns) array in a formula, type {10,12,38;6,42,15} including the braces. Array Constants:
Do not enter cell references or cell names in an array formula. You can enter cell references as ranges (A1:F23, for example), or you can enter constant values in an array formula (in the example on the last page, {=A2:A5*{4;3.75;3.25;2.50}}: you can't substitute B2;B3;B4;B5 for the values 4;3.75;3.25;2.50). An array constant can contain values only.
Examples of Array Constants:
See also How Microsoft Excel Expands Array Formulas and Special Functions to Help You Work with Arrays in the Excel User's Guide, pages 176-179, or in Excel Help. If you have special projects which will benefit from advanced use of arrays and array functions, please contact Corporate Training for personal assistance in designing your project.
Editing ArraysArrays cannot be edited the same way you edit information in individual cells or non-array ranges. You cannot change the contents of cells that are part of an array; you cannot clear, move, or delete cells that are part of an array; and you cannot insert cells into an array range. You can, however, format individual cells in an array range, and you can copy and paste a cell or a range of cells from an array to any other part of a worksheet. To Edit An Array, select Go To from the Edit menu, click on the Special button, then select the Current Array option to select the array containing the active cell. Edit the formula, then save the changes with [Ctrl + Shift + Enter]. OR click on any cell in the array range, then activate the
formula bar by clicking in it (the braces will disappear). Edit the
formula in the formula bar, then save the changes by pressing An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas the same way that you create basic, single-value formulas. Select the cell or cells that will contain the formula, create the formula, and then press [Ctrl + Shift + Enter] to enter the formula. If you want only a single result, Microsoft Excel may need to perform several calculations to generate that result. For example, the following formula averages only the cells in the range D5:D15 where the cell in the same row in column A contains the text "Blue Sky Airlines". The IF function finds the cells in the range A5:A15 that contain "Blue Sky Airlines" and then returns the value in the corresponding cell in D5:D15 to the AVERAGE function.
To calculate multiple results with an array formula, you must enter the array into a range of cells that has the same number of rows and columns as the array arguments. In the following example, given a series of five sales figures (in column B) for a series of five dates (in column A), the TREND function determines the straight-line values for the sales figures. To display all of the results of the formula, it is entered into five cells in column C (C10:C15).
You can also use an array formula to calculate single or multiple results for a series of values that have not been entered on the worksheet. Array formulas can accept constants the same way nonarray formulas do, but you must enter the array constants in a certain format. For example, given the same five values and the same five dates in the preceding example, you can project the sales figures for two additional dates in the future. Because formulas or functions cannot be array constants, the following example uses serial numbers to represent the additional dates for the third argument in the TREND function:
Enter an array formulaWhen you enter an array formula, Microsoft Excel automatically inserts the formula between { } (braces).
Values that do not change in array formulasA basic, single-value formula produces a single result from one or more arguments or values; you can enter either a reference to a cell that contains a value or the value itself. In an array formula, where you might usually use a reference to a range of cells, you can instead type the array of values contained within the cells. The array of values you type is called an array constant and generally is used when you do not want to enter each value into a separate cell on the worksheet. To create an array constant, you must do the following:
For example, you can enter {10,20,30,40} in an array formula instead of entering 10, 20, 30, 40 in four cells in one row. This array constant is known as a 1-by-4 array and is equivalent to a 1-row-by-4-column reference. To represent the values 10, 20, 30, 40 in one row and 50, 60, 70, 80 in the row immediately below, you would enter a 2-by-4 array constant: {10,20,30,40;50,60,70,80}. (see also Working with Arrays, Excel User’s Guide, p. 171-179)
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||