# Row with Indirect

#### MyExcel

##### Well-known Member
Hi
i saw this formula many time as apart from large formula
Code:
``ROW(INDIRECT("1:10"))``
what is mean
and when we use it

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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"))))

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:
Code:
``=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:
Code:
``````=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:
Code:
`` =ROW(INDIRECT("1:10"))``

Does that help?

Replies
13
Views
127
Replies
9
Views
152
Replies
7
Views
277
Replies
1
Views
126
Replies
6
Views
90

### Forum statistics

1,203,244
Messages
6,054,363
Members
444,718
Latest member
r0nster ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back