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

[Guest]

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?

 

[Bob Phillips]

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

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

 

[Ron Rosenfeld]

=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

 

[Bob Phillips]

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

 

[Chip Pearson]

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]

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

 

[Ron Rosenfeld]

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

I agree. I was writing more generally.

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.

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

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

 

[Bob Phillips]

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.

 

[Ron Rosenfeld]

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