Works Great
Thanks Alot
"Bob Phillips" wrote:
=(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))
--
HTH
Bob Phillips
"Andrew" wrote in message
...
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
|