software

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 lt;= n lt;= 10, say.. Maybe a Subtotal() fn could
be used.

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

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

quot;Nigel Greenwoodquot; gt; wrote in message oups.com...
gt; At our gliding (soaring) club we record club members' flights, scoring
gt; them according to a complex formula based on handicap, distance,
gt; height, etc. At the end of the season a grand total score is
gt; calculated for each member by totalling his/her 4 best (ie
gt; highest-scoring) flights. I'm looking for a way of automating this in
gt; an Excel w/sheet.
gt;
gt; Is there some way of combining the Rank() function with Database fns?
gt; A complicating factor is the fact that a given pilot may have n flights
gt; over the season, where 1 lt;= n lt;= 10, say.. Maybe a Subtotal() fn could
gt; be used.
gt;
gt; Before I reinvent the wheel, I'd be grateful for any suggestions.
gt;
gt; Nigel
gt;
Hi Nigel:

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

=SUM(LARGE(A1:A10,{1,2,3,4}))
--
Gary's Studentquot;Nigel Greenwoodquot; wrote:

gt; At our gliding (soaring) club we record club members' flights, scoring
gt; them according to a complex formula based on handicap, distance,
gt; height, etc. At the end of the season a grand total score is
gt; calculated for each member by totalling his/her 4 best (ie
gt; highest-scoring) flights. I'm looking for a way of automating this in
gt; an Excel w/sheet.
gt;
gt; Is there some way of combining the Rank() function with Database fns?
gt; A complicating factor is the fact that a given pilot may have n flights
gt; over the season, where 1 lt;= n lt;= 10, say.. Maybe a Subtotal() fn could
gt; be used.
gt;
gt; Before I reinvent the wheel, I'd be grateful for any suggestions.
gt;
gt; Nigel
gt;
gt;

Gary''s Student wrote:
gt; Hi Nigel:
gt;
gt; If a members scores are in A1 thru A10, then try:
gt;
gt; =SUM(LARGE(A1:A10,{1,2,3,4}))

Thanks for the speedy response, Bob amp; 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, amp; 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 quot;top
10quot;: could I modify that in some way? It seems that if I filter on a
member's name, then select quot;top 4quot; to filter the scores, the top 4
refer to the entire (unfiltered) list, not just that member's scores.

NigelThe try this

=SUM(LARGE(A1:A10,ROW(INDIRECT(quot;1:quot;amp;MIN(4,COUNT(A1 :A10))))))

and this really is an array formula.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

quot;Nigel Greenwoodquot; gt; wrote in message ups.com...
gt; Gary''s Student wrote:
gt; gt; Hi Nigel:
gt; gt;
gt; gt; If a members scores are in A1 thru A10, then try:
gt; gt;
gt; gt; =SUM(LARGE(A1:A10,{1,2,3,4}))
gt;
gt; Thanks for the speedy response, Bob amp; Gary.
gt;
gt; This would be an excellent method if I knew that each member has 10
gt; scores -- but a given member might have only 1 score, or maybe 6. I'd
gt; like to design the w/sheet to be as general as possible, amp; avoid having
gt; to type in new formulas each time for each member!
gt;
gt; I noticed that if you filter a list one of the display options is quot;top
gt; 10quot;: could I modify that in some way? It seems that if I filter on a
gt; member's name, then select quot;top 4quot; to filter the scores, the top 4
gt; refer to the entire (unfiltered) list, not just that member's scores.
gt;
gt; Nigel
gt;

Bob Phillips wrote:
gt; The try this
gt;
gt; =SUM(LARGE(A1:A10,ROW(INDIRECT(quot;1:quot;amp;MIN(4,COUNT(A1 :A10))))))
gt;
gt; and this really is an array formula.

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

Nigel