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.
Dick,
Thanks for the explanation. It’s a whole lot clearer now. I appreciate it.
John Mansfield
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))
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.)
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:
=SUM(LARGE($B$23:$B$37,ROW(1:5)))
Kevin,
You have to array enter (Use Control Shift Enter) the second one.
Thanks, JPG. That got it to work.
I am trying to get excel to recognize a letter in a row as a number for instance if a1=f then ae12 will equal 1 can anybody help with this?
@TJ In cell AE12: =if(a1=”f”, 1,0)
How would one accomplish the ROW(INDIRECT()) using VB Script.
I am trying to convert my array formula into a VB user defined function and it keeps highlighting the ROW and INDIRECT functions.
Hi,
I tried the same formula but the data are in a row but it doesn’t work for the function as below. (I changed ROW function to COLUMN) :
=SUM(LARGE($B$2:$G$2;COLUMN(1:6)))
Any idea?
Hello Icon
=SUMPRODUCT(LARGE($B$2:$N$2,ROW(1:6))) or equivalent
=SUMPRODUCT(LARGE($B$2:$N$2,{1;2;3;4;5;6}))
or Array Enter
=SUM(LARGE($B$2:$N$2,ROW(1:6)))
Row(1:6) is used to provide the series of numbers 1,2,3,4,5,6. Please see the information above.