Formula Question
 

I have a list of seven people (golfers) who will have scores. For the
team scoring I would like the top-five of those seven to be
calculated and omit the bottom two scores. I would also like for a
specific one of those golfers to count regardless of their score. Is
there a formula that would do this?

Let me know if I need to explain this better.

A quick recap...
-I have seven golfers
-One of those golfers (preselected) counts in the team score no matter
what
-The two worst scores (outside of the one that counts no matter what)
must be ommitted.

Thanks for the help!

Todd

Assume the scores are in B2:B8 and that the persosn with a score in B5 is
selected

=IF(ISNUMBER(MATCH(B5,SMALL(B2:B8,ROW(INDIRECT("1: 5"))),0)),SUM(SMALL(B2:B8,ROW(INDIRECT("1:5")))),S UM(SMALL(B2:B8,ROW(INDIRECT("1:5"))))+B5)

entered with ctrl + shift & enter

Regards,

Peo Sjoblom

"dobball23" wrote:

I have a list of seven people (golfers) who will have scores. For the
team scoring I would like the top-five of those seven to be
calculated and omit the bottom two scores. I would also like for a
specific one of those golfers to count regardless of their score. Is
there a formula that would do this?

Let me know if I need to explain this better.

A quick recap...
-I have seven golfers
-One of those golfers (preselected) counts in the team score no matter
what
-The two worst scores (outside of the one that counts no matter what)
must be ommitted.

Thanks for the help!

Todd

By calculated you mean SUMmed? And I assume that you want the lower 4
scores, not the higher 4, right?

How about

=specified player's score + sum(small(other 6 scores,{1,2,3,4}))



"dobball23" wrote:

I have a list of seven people (golfers) who will have scores. For the
team scoring I would like the top-five of those seven to be
calculated and omit the bottom two scores. I would also like for a
specific one of those golfers to count regardless of their score. Is
there a formula that would do this?

Let me know if I need to explain this better.

A quick recap...
-I have seven golfers
-One of those golfers (preselected) counts in the team score no matter
what
-The two worst scores (outside of the one that counts no matter what)
must be ommitted.

Thanks for the help!

Todd

=SUMPRODUCT(--SMALL(A2:A8,{1,2,3,4,5}))-IF(RANK(A2,A2:A8,1)<6,A2,SMALL(A2:A8,5))+A2

assuming that your scores are in range A2:a8 and the score of cell A2 has to
be always considered


"dobball23" wrote in message
...
I have a list of seven people (golfers) who will have scores. For the
team scoring I would like the top-five of those seven to be
calculated and omit the bottom two scores. I would also like for a
specific one of those golfers to count regardless of their score. Is
there a formula that would do this?

Let me know if I need to explain this better.

A quick recap...
-I have seven golfers
-One of those golfers (preselected) counts in the team score no matter
what
-The two worst scores (outside of the one that counts no matter what)
must be ommitted.

Thanks for the help!

Todd

Suggestion for a worksheet method:

Cells A2 to A8: numbers 1 to 7
Cells B2 to B8: names of the players. The player who has to be included is
number 1.
Cells C2 to C8: the scores
Cell D2: =RANK(C2,$C$2:$C$8,5)
Cell E2: =C2+ROW(A2)/1000
Cell F2: =RANK(E2,$E$2:$E$8,5)
Copy C2:F2 to C2:F8
Cells G2 to G8: numbers 1 to 7
Cell H2: =SUMPRODUCT($A$2:$A$8,--($F$2:$F$8=$G2))
Cell I2: =SUMPRODUCT($D$2:$D$8,--($A$2:$A$8=$H2))
Cell J2: =VLOOKUP($H2,$A$2:$B$8,2)
Cell K2: =SUMPRODUCT($C$2:$C$8,--($A$2:$A$8=$H2))
Copy H2:K2 to H2:K6
Cell L2: =I2
Copy to L2:N5
Cell L6: =IF($D$25,$D$2,$I6)
Cell M6: =IF($D$25,$B$2,$J6)
Cell N6: =IF($D$25,$C$2,$K6)


"dobball23" wrote:

I have a list of seven people (golfers) who will have scores. For the
team scoring I would like the top-five of those seven to be
calculated and omit the bottom two scores. I would also like for a
specific one of those golfers to count regardless of their score. Is
there a formula that would do this?

Let me know if I need to explain this better.

A quick recap...
-I have seven golfers
-One of those golfers (preselected) counts in the team score no matter
what
-The two worst scores (outside of the one that counts no matter what)
must be ommitted.

Thanks for the help!

Todd