How can I total the top 9 scores of 12 weeks in a sporting contest
 

I need to keep track of the scores in a quiz for 12 weeks running, then total
each contestant's best 9 scores.

Is there any way to sum a column, automaticaly ignoring the 3 worst scores?

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

it is an array formula, so commit it with Ctrl-Shift-Enter.

--
HTH

Bob Phillips

"Vance Burton" <Vance wrote in message
...
I need to keep track of the scores in a quiz for 12 weeks running, then
total
each contestant's best 9 scores.

Is there any way to sum a column, automaticaly ignoring the 3 worst
scores?

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

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


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.

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(A1 :A12))))))

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

On Mon, 17 Jan 2005 09:43:17 -0600, "Chip Pearson" wrote:

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.

Ahh. Thank you for that information.


--ron

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

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

wrote:

However, I have JUST discovered that this problem can also be avoided
by
using
absolute references: ROW($1:$9).

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

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

I think that the one thing that can be taken from this conversation is that
generalisations are wrong in at least one case. Chip's point is valid, but
there are bound to be instances when inserting a row will want the range
covered to be increased, there are others when it will not.

So, as my old Tandem guru first said to me, it depends ... SO my better
depends on the OPs actual needs, as you rightly state, but so does yours.

On Mon, 17 Jan 2005 22:58:39 -0000, "Bob Phillips"
wrote:

I think that the one thing that can be taken from this conversation is that
generalisations are wrong in at least one case. Chip's point is valid, but
there are bound to be instances when inserting a row will want the range
covered to be increased, there are others when it will not.

So, as my old Tandem guru first said to me, it depends ... SO my better
depends on the OPs actual needs, as you rightly state, but so does yours.

Concur
--ron