Tally Question

[Sayth]

Hi I was wondering if this was possible in excel 2007(Or 2010, I am thinking about buying it).

Can Excel Tally, what do I mean? Well say we have a school carnival there are 5 grades/classes and they are having a sports carnival. Each kid in each class will compete in all 5 events. The winner of each class will be the highest point scorer across each event. The teachers record height jumped, time run and so forth for each kid.

So say if for finishing first in an event we asigned the student 5 points, 2nd 4pts, 3rd 3pts, 4th 2pts and 5th 1pts. Do this for each event and each class and have a list/table of winners for each class.

Is this possible? Is there a good way to design a scenario like this?

Even in a table manually calculating it the highest results come to the top regardless of class. NB no this isn't a school test or anything I am 36 with three kids as it happens .

I have created a sample table I was working with to see if I could do it.

<table border="0" cellpadding="0" cellspacing="0" width="638"><col style="width: 53pt;" width="71"> <col style="width: 62pt;" width="82"> <col style="width: 48pt;" span="5" width="64"> <col style="width: 76pt;" width="101"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 53pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;" height="20" width="71">number</td> <td class="xl66" style="width: 62pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;" width="82">Column1</td> <td class="xl66" style="width: 48pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;" width="64">High Jump</td> <td class="xl66" style="width: 48pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;" width="64">200m Sprint</td> <td class="xl66" style="width: 48pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;" width="64">Ring Toss</td> <td class="xl66" style="width: 48pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;" width="64">Cricket</td> <td class="xl66" style="width: 48pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;" width="64">Scrabble</td> <td class="xl66" style="width: 76pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;" width="101">Tally</td> <td class="xl67" style="width: 48pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;" width="64">Tally Score</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right" height="20">1</td> <td class="xl69" style="font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);">Mark </td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">1.78</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">24</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">2</td> <td class="xl80" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">23</td> <td class="xl80" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">16</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);"> </td> <td class="xl71" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;" align="right" height="20">1</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">Sammy</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">1.79</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">24.1</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">5</td> <td class="xl80" style="border-top: medium none; border-left: medium none;" align="right">26</td> <td class="xl80" style="border-top: medium none; border-left: medium none;" align="right">23</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl71" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right" height="20">1</td> <td class="xl69" style="font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);">Tim</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">1.45</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">24.5</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">11</td> <td class="xl80" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">25</td> <td class="xl80" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">36</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);"> </td> <td class="xl71" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;" align="right" height="20">1</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">Bill</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">1.85</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">24.6</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">12</td> <td class="xl80" style="border-top: medium none; border-left: medium none;" align="right">13</td> <td class="xl81" style="border-top: medium none; border-left: medium none;" align="right">9</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl71" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right" height="20">1</td> <td class="xl69" style="font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);">David</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">1.23</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">25.4</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">9</td> <td class="xl80" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">23</td> <td class="xl80" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">19</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);"> </td> <td class="xl71" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;" align="right" height="20">1</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">Tom</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">1.65</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">26</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">8</td> <td class="xl80" style="border-top: medium none; border-left: medium none;" align="right">18</td> <td class="xl80" style="border-top: medium none; border-left: medium none;" align="right">19</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl71" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right" height="20">1</td> <td class="xl69" style="font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);">Tammy</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">0.85</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">26.8</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">4</td> <td class="xl80" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">17</td> <td class="xl80" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">18</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);"> </td> <td class="xl71" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;" align="right" height="20">1</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">Sam</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">0.56</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">25</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">23</td> <td class="xl80" style="border-top: medium none; border-left: medium none;" align="right">16</td> <td class="xl81" style="border-top: medium none; border-left: medium none;" align="right">16</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl71" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right" height="20">1</td> <td class="xl69" style="font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);">Paul</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">1.01</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">25.4</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">13</td> <td class="xl80" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">23</td> <td class="xl80" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">15</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);"> </td> <td class="xl71" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;" align="right" height="20">1</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">Matthew</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">1.52</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">26.7</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">26</td> <td class="xl80" style="border-top: medium none; border-left: medium none;" align="right">36</td> <td class="xl80" style="border-top: medium none; border-left: medium none;" align="right">15</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl71" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right" height="20">1</td> <td class="xl69" style="font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);">Ben</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">1.49</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">26.1</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">12</td> <td class="xl81" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: 0.5pt 0.5pt medium; border-style: solid solid none; border-color: windowtext windowtext -moz-use-text-color; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">9</td> <td class="xl80" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">15</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);"> </td> <td class="xl71" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;" align="right" height="20">1</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">John</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">1.52</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">26.3</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">3</td> <td class="xl80" style="border-left: medium none;" align="right">19</td> <td class="xl80" style="border-top: medium none; border-left: medium none;" align="right">14</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl71" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right" height="20">1</td> <td class="xl69" style="font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);">Luke</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">1.32</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">26.8</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">5</td> <td class="xl80" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">19</td> <td class="xl80" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">55</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);"> </td> <td class="xl71" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% yellow;" align="right" height="20">2</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">Constantine</td> <td class="xl73" style="border-top: medium none; border-left: medium none;" align="right">1.56</td> <td class="xl73" style="border-top: medium none; border-left: medium none;" align="right">25.9</td> <td class="xl73" style="border-top: medium none; border-left: medium none;" align="right">11</td> <td class="xl80" style="border-top: medium none; border-left: medium none;" align="right">18</td> <td class="xl82" style="border-top: medium none; border-left: medium none;" align="right">65</td> <td class="xl73" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl74" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% yellow;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% yellow;" align="right" height="20">2</td> <td class="xl69" style="font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);">Ben</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">1.2</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">26.4</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">15</td> <td class="xl80" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">18</td> <td class="xl80" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">29</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);"> </td> <td class="xl71" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% yellow;" align="right" height="20">2</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">Tim</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">1.35</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">25.8</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">16</td> <td class="xl80" style="border-top: medium none; border-left: medium none;" align="right">18</td> <td class="xl80" style="border-top: medium none; border-left: medium none;" align="right">47</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl71" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% yellow;" align="right" height="20">2</td> <td class="xl69" style="font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);">Tammy</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">1.8</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">25.4</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">23</td> <td class="xl80" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">17</td> <td class="xl80" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">50</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);"> </td> <td class="xl71" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% yellow;" align="right" height="20">2</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">Mark </td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">1.24</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">26.2</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">26</td> <td class="xl80" style="border-top: medium none; border-left: medium none;" align="right">17</td> <td class="xl81" style="border-top: medium none; border-left: medium none;" align="right">54</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl71" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% yellow;" align="right" height="20">2</td> <td class="xl69" style="font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);">Matthew</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">0.58</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">25.1</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">25</td> <td class="xl80" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">17</td> <td class="xl80" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">58</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);"> </td> <td class="xl71" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% yellow;" align="right" height="20">2</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">Bill</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">0.98</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">24.9</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">13</td> <td class="xl80" style="border-top: medium none; border-left: medium none;" align="right">16</td> <td class="xl80" style="border-top: medium none; border-left: medium none;" align="right">61</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl71" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% yellow;" align="right" height="20">2</td> <td class="xl69" style="font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);">Bill</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">1.02</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">23.5</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">23</td> <td class="xl80" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">16</td> <td class="xl80" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">65</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);"> </td> <td class="xl71" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% yellow;" align="right" height="20">2</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">Sammy</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">1.05</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">26.8</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">18</td> <td class="xl81" style="border-top: medium none; border-left: medium none;" align="right">16</td> <td class="xl80" style="border-top: medium none; border-left: medium none;" align="right">69</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl71" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% yellow;" align="right" height="20">2</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">Bruce</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">1.56</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">25.3</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">17</td> <td class="xl80" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">15</td> <td class="xl80" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">73</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);"> </td> <td class="xl71" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% yellow;" align="right" height="20">2</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">David</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">1.78</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">25.4</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">16</td> <td class="xl80" style="border-top: medium none; border-left: medium none;" align="right">15</td> <td class="xl82" style="border-top: medium none; border-left: medium none;" align="right">76</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl71" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% yellow;" align="right" height="20">2</td> <td class="xl69" style="font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);">Matthew</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">1.45</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">25.2</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">23</td> <td class="xl80" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">15</td> <td class="xl80" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">80</td> <td class="xl70" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);"> </td> <td class="xl71" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% yellow;" align="right" height="20">2</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">Sammy</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">1.52</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">28.5</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" align="right">36</td> <td class="xl80" style="border-top: medium none; border-left: medium none;" align="right">14</td> <td class="xl80" style="border-top: medium none; border-left: medium none;" align="right">84</td> <td class="xl70" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl71" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext;"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl76" style="height: 15.75pt; font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% yellow;" align="right" height="21">2</td> <td class="xl77" style="font-size: 11pt; color: windowtext; font-weight: 700; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);">Sam</td> <td class="xl78" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">1.55</td> <td class="xl78" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">27.6</td> <td class="xl78" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">9</td> <td class="xl80" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">14</td> <td class="xl80" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);" align="right">88</td> <td class="xl78" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);"> </td> <td class="xl79" style="font-size: 11pt; color: windowtext; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(229, 224, 236);"> </td> </tr> </tbody></table>

 

[jimrward]

i can see the use of a five helper columns which would RANK each event for each competitor, and then you can use these to derive the required tally, should be fairly easy once the RANK formula is in place using either RANK or SUMPRODUCT

the TALLY would of the form of check the Rank for the High Jump for a competitor, if is less than 6 then CHOOSE the points

=IF(HJRANK<6, CHOOSE(HJRANK,5,4,3,2,1),0)+IF(SPRANK<6, CHOOSE(SPRANK,5,4,3,2,1),0)+... etc for the rest of the events

I will try and put together a proper working example

 

[jimrward]

ok, here is my take on things, not sure what you want in the final column tally score

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	number	Column1	HighJump	200mSprint	RingToss	Cricket	Scrabble	HJRank	SPRank	RTRank	CRRank	SCRank	Tally	TallyScore
2	1	Mark	1.78	24	2	23	16	3	13	13	4	7	5
3	1	Sammy	1.79	24.1	5	26	23	2	12	9	2	3	11
4	1	Tim	1.45	24.5	11	25	36	8	11	6	3	2	7
5	1	Bill	1.85	24.6	12	13	9	1	10	4	12	13	7
6	1	David	1.23	25.4	9	23	19	10	7	7	4	4	4
7	1	Tom	1.65	26	8	18	19	4	6	8	9	4	4
8	1	Tammy	0.85	26.8	4	17	18	12	1	11	10	6	5
9	1	Sam	0.56	25	23	16	16	13	9	2	11	7	4
10	1	Paul	1.01	25.4	13	23	15	11	7	3	4	9	5
11	1	Matthew	1.52	26.7	26	36	15	5	3	1	1	9	14
12	1	Ben	1.49	26.1	12	9	15	7	5	4	13	9	3
13	1	John	1.52	26.3	3	19	14	5	4	12	7	12	3
14	1	Luke	1.32	26.8	5	19	55	9	1	9	7	1	10
15	2	Constantine	1.56	25.9	11	18	65	3	6	13	1	7	8
16	2	Ben	1.2	26.4	15	18	29	10	4	11	1	14	7
17	2	Tim	1.35	25.8	16	18	47	8	7	9	1	13	5
18	2	Tammy	1.8	25.4	23	17	50	1	8	4	4	12	9
19	2	Mark	1.24	26.2	26	17	54	9	5	2	4	11	7
20	2	Matthew	0.58	25.1	25	17	58	14	12	3	4	10	5
21	2	Bill	0.98	24.9	13	16	61	13	13	12	7	9	0
22	2	Bill	1.02	23.5	23	16	65	12	14	4	7	7	2
23	2	Sammy	1.05	26.8	18	16	69	11	3	7	7	6	3
24	2	Bruce	1.56	25.3	17	15	73	3	10	8	10	5	4
25	2	David	1.78	25.4	16	15	76	2	8	9	10	4	6
26	2	Matthew	1.45	25.2	23	15	80	7	11	4	10	3	5
27	2	Sammy	1.52	28.5	36	14	84	6	1	1	13	2	14
28	2	Sam	1.55	27.6	9	14	88	5	2	14	13	1	10
Sheet1

 

[jimrward]

ooops slight mistake on my part

you will need to change the greater than and less than sign around depending on whether highest or lowest value is the winner, I set them all to highest with less than sign, you will need to change to greater than sign in the sprint rank column and any others where lowest value wins

 

[Sayth]

I like your solution, though I not sure I fully understand it. I am not sure how
the

(C2<C$2:C$30)

works in the formula to create the ranking list.

=SUMPRODUCT(($A$2:$A$30=$A2)*(C2<C$2:C$30))+1

It is late at nigh here however and your effort is deserving of fresher eyes so I will have a look in the morning.

Thank you.

 

[jimrward]

i could have used the RANK formula, that ranks on one criteria only, however your requirement is to RANK on two criteria, the number column and one other column be it time or distance, this allows us to RANK all high jumpers in Pool1, and Pool2 separately, the number column could be male/female or house names for a school, and also does not have to be numeric

if you anticipate more than the ranges I have set, you will have to change the references $30 to what ever is your last row, note that this has to be in all places in the formula in all columns otherwise SUMPRODUCT will fail its fussy that way, best way is to change in row 2 and drag down.

where in the world are you, the mention of cricket and scrabble suggests southern hemisphere ??

 

[Sayth]

Yeah NSW Australia good guess, though I thought the UK had scrabble and cricket as well