|
Function Question
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 |
=(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 |
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 |
| All times are GMT +1. The time now is 10:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com