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

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)

"Nigel Greenwood" wrote in message
oups.com...
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

Hi Nigel:

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

=SUM(LARGE(A1:A10,{1,2,3,4}))
--
Gary's Student


"Nigel Greenwood" wrote:

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

Gary''s Student wrote:
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

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" wrote in message
ups.com...
Gary''s Student wrote:
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 wrote:
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