Formula Question

[dobball23]

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

Let me know if I need to explain this better

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

Thanks for the help

Tod

 

[Guest]

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")))),SUM(SMALL(B2:B8,ROW(INDIRECT("1:5"))))+B5)

entered with ctrl + shift & enter

Regards,

Peo Sjoblom

 

[Guest]

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

 

[N Harkawat]

=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

 

[Guest]

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$2>5,$D$2,$I6)
Cell M6: =IF($D$2>5,$B$2,$J6)
Cell N6: =IF($D$2>5,$C$2,$K6)