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)

"Nigel Greenwood" <[email protected]> wrote in message
news:[email protected]...
> 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]

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

[Nigel Greenwood]

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]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Nigel Greenwood]

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