Best 4 scores

[Nigel Greenwood]

At our gliding (soaring) club we record club members' flights, scoring
them according to a complex formula based on handicap, distance,
height, etc. At the end of the season a grand total score is
calculated for each member by totalling his/her 4 best (ie
highest-scoring) flights. I'm looking for a way of automating this in
an Excel w/sheet.

Is there some way of combining the Rank() function with Database fns?
A complicating factor is the fact that a given pilot may have n flights
over the season, where 1 <= n <= 10, say.. Maybe a Subtotal() fn could
be used.

Before I reinvent the wheel, I'd be grateful for any suggestions.

Nigel

 

[Bob Phillips]

Assuming the scores are in A1:A10, just use

=SUM(LARGE(A1:A10,{1,2,3,4}))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

and then rank those values.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Guest]

Hi Nigel:

If a members scores are in A1 thru A10, then try:

=SUM(LARGE(A1:A10,{1,2,3,4}))

 

[Nigel Greenwood]

Hi Nigel:

If a members scores are in A1 thru A10, then try:

=SUM(LARGE(A1:A10,{1,2,3,4}))

Thanks for the speedy response, Bob & Gary.

This would be an excellent method if I knew that each member has 10
scores -- but a given member might have only 1 score, or maybe 6. I'd
like to design the w/sheet to be as general as possible, & avoid having
to type in new formulas each time for each member!

I noticed that if you filter a list one of the display options is "top
10": could I modify that in some way? It seems that if I filter on a
member's name, then select "top 4" to filter the scores, the top 4
refer to the entire (unfiltered) list, not just that member's scores.

Nigel

 

[Bob Phillips]

The try this

=SUM(LARGE(A1:A10,ROW(INDIRECT("1:"&MIN(4,COUNT(A1:A10))))))

and this really is an array formula.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Nigel Greenwood]

The try this

=SUM(LARGE(A1:A10,ROW(INDIRECT("1:"&MIN(4,COUNT(A1:A10))))))

and this really is an array formula.

Thanks, Bob -- just what I was looking for. All these years, & I'm
still learning how to use Excel!

Nigel