Function Question

[Guest]

i have a sheet as follows

A B
Score Bonus
210
220
210

The bonus column has to calculate which of the three cells is the highest
and give it 40 for a bonus. the second highest 20 and the lowest score gets
nothing. if there is a 2 way tie for second place they share the points. (ie
10 points each) if there is a 2 way tie for first they share First and
second poiints (30 points each) if it is a three way tie for first they
share all the points (ie 20 points each). In total there is no more than 60
bonus points available over the scores.

I have got some examples below.

Eaxample 1
A B
Score Bonus
210 20
209 0
240 40


Eaxample 2
A B
Score Bonus
200 20
200 20
200 20

Eaxample 3
A B
Score Bonus
210 40
200 10
200 10

Eaxample 4
A B
Score Bonus
220 30
220 30
200 0

I hope i have explained this clear enough.

Regards

 

[Bob Phillips]

=(A2=LARGE($A$2:$A$4,1))*(40/COUNTIF($A$2:$A$4,A2))+(A2=LARGE($A$2:$A$4,2))*
(20/COUNTIF($A$2:$A$4,A2))

 

[Guest]

Works Great

Thanks Alot

=(A2=LARGE($A$2:$A$4,1))*(40/COUNTIF($A$2:$A$4,A2))+(A2=LARGE($A$2:$A$4,2))*
(20/COUNTIF($A$2:$A$4,A2))