Row with Indirect

[MyExcel]

Hi
i saw this formula many time as apart from large formula

ROW(INDIRECT("1:10"))

what is mean
and when we use it

 

[shyrath]

From Cpearson
http://www.cpearson.com/excel/indirect.htm

....This feature is important when working with some array formulas. Frequently, an array formula will use the ROW() function to return an array of numbers. For example, the following formula will return the average of the 10 largest numbers in the range A1:A60 :

=AVERAGE(LARGE(A1:A60,ROW(1:10)))

However, if you insert a row between rows 1 and 10, Excel will change the formula to

=AVERAGE(LARGE(A1:A60,ROW(1:11)))

which will return the average of the 11 largest numbers. If we use the function with a string, Excel won't change the reference, so the formula will remain correct, regardless of whether and where rows are inserted or deleted.

=AVERAGE(LARGE(A1:A60,ROW(INDIRECT("1:10"))))

 

[Colin Legg]

This would be part of an array formula. It returns an array of values:

{1;2;3;4;5;6;7;8;9;10}


You can see this if you enter the formula into a cell and then click in the formula bar and press F9.

It is often desired to dynamically create an array of sequential numbers such as this. This formula would produce the same answer:

=ROW(1:10)

But I expect that INDIRECT was included to make the formula more robust. That is, if a user inserted a row between rows 1 and 10, then this formula would change:

=ROW(1:10)
'becomes
=ROW(2:11)     or      =ROW(1:11)

This could mess up the logic of the formula so this was probably used because it will not change if a row is inserted:

 =ROW(INDIRECT("1:10"))

Does that help?