On Mon, 17 Jan 2005 12:59:40 -0000, "Bob Phillips"
wrote:


"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.

Can't see that this is a problem given the OPs question.

I agree. I was writing more generally.



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.

It may work okay, but again it cannot be an issue for one or maybe 100
formulas. This sort of 'efficiency' drive is rarely necessary IMO. When you
have a poor performing SS, look for improvements, but it does not need to
become gospel.

As it turns out, Chip posted the way in which even the absolute reference
method can get messed up.



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) )))

Granted, this is more serious, but rather than blank it out with less than
9, it would be better to sum what we have, such as

=SUM(LARGE(A1:A12,ROW(INDIRECT("1:"&MIN(9,COUNT(A 1:A12))))))


It might be better but I think which method would actually be "better" depends
on what the OP wants. For example, if there are no valid values until there is
a minimum of nine entries, then either Blank, 0, or some informative message
(like "too few entries" or "only " & COUNT(A1:A12) & " entries") might be
"better".


--ron