Recording & Arranging Scores

[Bruce Ratcliffe]

1. A number of competitors compete in 6 separate events and these scores need to be recorded and arranged highest to lowest. i) Event 1 results in 3 scores which need to be added together for a total score. (scores may include fractions) ii) Event 2 results in 3 scores which need to be added together for a total score. (scores may include fractions) iii) Event 3 results in 3 scores which need to be added together for a total score. (scores may include fractions) The scores from the first 3 events are totalled for each competitor. The competitor with the highest aggregate score is assigned 100 points. Every other competitor has their score compared with the highest score and they are assigned points equivalent to the percentage of the highest score. Eg. If the highest score were 182.6 then this is assigned 100 points, another score of say 165.7 would be 90.75% of 182.6 and therefore the second competitor would be assigned 90.75 points. i) Event 4 results in a single score. (score may include fractions) ii) Event 5 results in a single score. (score may include fractions) iii) Event 6 results in a single score. (score may include fractions) The scores for the last 3 events are added to the points score assigned to each competitor as a result from the first 3 events to give a total score. 2. The competitors need to be arrayed on the spread sheet by ranking them highest score at the top to lowest score at the bottom. 3. In a separate table the top 3 scores as an aggregate of the last 3 events need to be identified. 4. Each competitor has a classification assignment A, B, C, or D. In a separate table the top 3 scores as an aggregate of the last 3 events need to be identified for each classification.

I would appreciate some assistance with writing a spread sheet for the above. Thank you.

(Previously I have formatted a spread sheet to deal with the last 3 events and the excel formula I used for that was: =IF($N5<>"",INDEX(A:A,LARGE(INDEX(($G$5:$G$470=$N5)*(ROW($N$5:$N$470)),0),COUNTIF($N$5:$N5,$N5))),"")