Using Row() in Array Formulas
By request.
In some array formulas, like the one to show cumulative principal payments, you will see a ROW() function or an ROW(INDIRECT()) construct. These provide a convenient way of inserting an array of consecutive numbers into an array.
Assume that you have a list of numbers and that you want to sum largest three of them.
You can use the LARGE function to find the nth largest value (C3 finds the largest value). Of course you can also use MAX, but that will only find the largest value. If you want to sum the largest three, you can simply string three LARGE functions together (C4). Because you are an Excel guru, you strive to shorten your formulas whenever possible (to impress your coworkers who can’t make heads or tails of them). The array formula in C5 does just that. Notice how it uses an array, namely {1,2,3}. I don’t know what you call this kind of array, but I call it a scalar array. The array is hard coded into the formula.
For a three element scalar array, typing out the array is not a big chore. But if that were to grow, it would become more of a burden. A shortcut for creating this type of array is to use the ROW function. The fragment ROW(1:3) when evaluated as an array evaluates to {1,2,3}, as shown in C6.
The next step is to have a variable number of rows to sum, possibly where the user decides how many rows. For instance, the user can type a number into C7, and the formula in C8 will sum the C7th largest values. In order to have a variable in the ROW function, we need to use INDIRECT to build a string that looks like the range reference we want. INDIRECT(”1:” & C7) evaluates to INDIRECT(”1:3″).
Hopefully that explains the use of ROW() or ROW(INDIRECT()) in an array formula.
John Mansfield:
Dick,
Thanks for the explanation. It’s a whole lot clearer now. I appreciate it.
John Mansfield
25 August 2004, 10:08 amJuan Pablo G:
If you want to confuse things a bit more, I tend to avoid using INDIRECT and OFFSET when possible, just because of their volatility, which, in large workbooks can create a real PITA.
To create an array like this what I usually do is:
ROW($A$1:INDEX($A:$A, C7))
Using the same cell that Dick uses in this example. If there’s any chance that the user can insert a row before row 1 (which would mess this formula), I go with this:
ROW(INDEX($A:$A, 1):INDEX($A:$A, C7))
25 August 2004, 10:19 amJonathan Rynd:
I call those kinds of arrays “Array constants”, since they are not based on cells in the spreadsheet, but on the constant numbers, strings, booleans, and error values that you specify in the formula. (You can’t put a cell reference, or the result of a calculation, as a member of an array constant.)
25 August 2004, 1:33 pmKevin:
Dick,
I can get the formula in C5 to work, but when I try the formula in C6 I get the same result as simply typing large(a3:a15,1). Am I missing something? Here is what I used:
This works:
=ROUND(AVERAGE(LARGE($B$23:$B$37,{1,2,3,4,5})),2)
This does not:
26 August 2004, 8:28 am=SUM(LARGE($B$23:$B$37,ROW(1:5)))
Juan Pablo G:
Kevin,
You have to array enter (Use Control Shift Enter) the second one.
26 August 2004, 8:48 amKevin:
Thanks, JPG. That got it to work.
26 August 2004, 10:28 am