However, I have JUST discovered that this problem can also be
avoided by using
absolute references: ROW($1:$9). Can you think of any
disadvantages to this
approach?

If you insert a row somewhere in 1:9, the reference will change
and the formula will return an incorrect result. It is thus
preferable to use the INDIRECT function.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Ron Rosenfeld" wrote in message
...
On Mon, 17 Jan 2005 10:08:59 -0000, "Bob Phillips"

wrote:

=SUM(LARGE(A1:A12,ROW(1:9))

There are two potential problems with this formula.

1. If you copy/drag it to another row, the row references in
the ROW function
will change. Usually, I have avoided this by using a construct
like
ROW(INDIRECT("1:9")).

However, I have JUST discovered that this problem can also be
avoided by using
absolute references: ROW($1:$9). Can you think of any
disadvantages to this
approach? If not, it would seem preferable to the INDIRECT
construct as it
saves a level of nesting.

2. The formula will give an error if there are less than 9
entries. If this
is undesirable, one could do something like:

=IF(COUNT(A1:A12)<9,"",SUM(LARGE(A1:A12,ROW($1:$9) )))

(**array-entered**)


--ron