Ordering formula - help needed

G

gn82

New Member
Joined
Aug 6, 2011
Messages
22
Hi,

I am trying to order a list of athletes based on their results in the contests they all participate in throughout the year but I am getting stuck on 2 points:

1) ordering them based on the total points they gained while using the positions they occupied as tiebreaker criteria (i.e. if they have the same points the one with more #1 spots will be ahead, if they're equal the differentiation continues with #2 spots, #3 spots and so on).

2) displaying the positions they occupied while ignoring the positions they did not occupied (i.e. 1 P1, 2 P2 instead of 1 P1, 2 P2, 0 P3, 0 P4, 0 P5...)

Here's a practical example just so you'll see the data:

<table border="0" cellpadding="0" cellspacing="0" width="456"><col style="width: 64pt;" width="85"> <col style="width: 37pt;" span="3" width="49"> <col style="width: 47pt;" width="62"> <col style="width: 61pt;" span="2" width="81"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 64pt;" height="17" width="85">
</td> <td class="xl25" style="width: 37pt;" width="49">Contest 1 - position</td> <td class="xl26" style="width: 37pt;" width="49">Contest 1 - points</td> <td class="xl25" style="width: 37pt;" width="49">Contest 2 - position</td> <td class="xl26" style="width: 47pt;" width="62">Contest 2 - points</td> <td class="xl25" style="width: 61pt;" width="81">Contest 3 - position</td> <td class="xl26" style="width: 61pt;" width="81">Contest 3 - points</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17">Athlete 1</td> <td class="xl27" align="center">1</td> <td class="xl28" align="center">5</td> <td class="xl28" align="center">3</td> <td class="xl28" align="center">1</td> <td class="xl28" align="center">2</td> <td class="xl28" align="center">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17">Athlete 2</td> <td class="xl27" align="center">2</td> <td class="xl28" align="center">4</td> <td class="xl28" align="center">1</td> <td class="xl28" align="center">7</td> <td class="xl28" align="center">2</td> <td class="xl28" align="center">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17">Athlete 3</td> <td class="xl27" align="center">2</td> <td class="xl28" align="center">4</td> <td class="xl28" align="center">3</td> <td class="xl28" align="center">1</td> <td class="xl28" align="center">1</td> <td class="xl28" align="center">4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17">Athlete 4</td> <td class="xl27" align="center">3</td> <td class="xl28" align="center">2</td> <td class="xl28" align="center">2</td> <td class="xl28" align="center">6</td> <td class="xl28" align="center">3</td> <td class="xl28" align="center">2</td> </tr> </tbody></table>
<table border="0" cellpadding="0" cellspacing="0" width="295"><col style="width: 37pt;" width="49"> <col style="width: 50pt;" width="67"> <col style="width: 64pt;" width="85"> <col style="width: 71pt;" width="94"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 37pt;" height="17" width="49">
</td> <td class="xl25" style="width: 50pt;" width="67">Overall - position
</td> <td class="xl25" style="width: 64pt;" width="85">Overall - points</td> <td class="xl26" style="width: 71pt;" width="94">Overall - position history</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">Athlete 1</td> <td class="xl28" align="center">4</td> <td class="xl28" align="center">9</td> <td class="xl29">1 P1, 1 P2, 2 P3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">Athlete 2</td> <td class="xl28" align="center">1</td> <td class="xl28" align="center">14</td> <td class="xl29">1 P1, 2 P2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">Athlete 3</td> <td class="xl28" align="center">2</td> <td class="xl28" align="center">9</td> <td class="xl29">1 P1, 1 P2, 1 P3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">Athlete 4</td> <td class="xl28" align="center">3</td> <td class="xl28" align="center">10</td> <td class="xl29">2 P2, 1 P3</td> </tr> </tbody></table>

Thanks in advance !
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
are you sure your overall results are correct, surely athlete 4 is 2nd overall as he/she has 10 points, and no ties
 
Upvote 0
jimrward said:
are you sure your overall results are correct, surely athlete 4 is 2nd overall as he/she has 10 points, and no ties
Click to expand...

You are absolutely right, there was a typo. My ordering problem still remains though :(

<table border="0" cellpadding="0" cellspacing="0" width="295"><col style="width: 37pt;" width="49"> <col style="width: 50pt;" width="67"> <col style="width: 64pt;" width="85"> <col style="width: 71pt;" width="94"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 37pt;" height="17" width="49">
</td> <td class="xl25" style="width: 50pt;" width="67">Overall - position</td> <td class="xl25" style="width: 64pt;" width="85">Overall - points</td> <td class="xl26" style="width: 71pt;" width="94">Overall - position history</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">Athlete 1</td> <td class="xl28">3</td> <td class="xl28">9</td> <td class="xl29">1 P1, 1 P2, 1 P3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">Athlete 2</td> <td class="xl28">1</td> <td class="xl28">14</td> <td class="xl29">1 P1, 2 P2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">Athlete 3</td> <td class="xl28">3</td> <td class="xl28">9</td> <td class="xl29">1 P1, 1 P2, 1 P3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">Athlete 4</td> <td class="xl28">2</td> <td class="xl28">10</td> <td class="xl29">2 P2, 1 P3</td> </tr> </tbody></table>
 
Upvote 0
ok more questions,

how many competitors
will it always be 3 events
is it only to get the top 4 places or will it be for say 100 competitors
 
Upvote 0
jimrward said:
ok more questions,

how many competitors
will it always be 3 events
is it only to get the top 4 places or will it be for say 100 competitors
Click to expand...

The number of competitors will be anywhere between the current 20 and a maximum of 40 competitors and the total number of events in the competition is a standard of 34 per year.

The goal is to show the overall chart with the positions for all competitors which will be updated after each event.
 
Upvote 0
Gn82,

This is a new layout, but I think that can help you. If not, lets wait for another way.

Note: the formulas of the range H4:P4 and R4:R7 are array formulas - Entered with Ctrtl+Shift+Enter and not only Enter.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;background-color: #C5D9F1;;">Tab - Data</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;background-color: #C5D9F1;;">Tab - Resume</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;text-align: center;;">Contest</td><td style="font-weight: bold;text-align: center;;">Athlete</td><td style="font-weight: bold;text-align: center;;">Pos</td><td style="font-weight: bold;text-align: center;;">Pts</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Overall - position history</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">Contest 1</td><td style="text-align: center;;">Athlete 1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">5</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Athlete</td><td style="font-weight: bold;text-align: center;;">Overall - position</td><td style="font-weight: bold;text-align: center;;">Overall - points</td><td style="font-weight: bold;text-align: center;;">P1</td><td style="font-weight: bold;text-align: center;;">P2</td><td style="font-weight: bold;text-align: center;;">P3</td><td style="font-weight: bold;text-align: center;;">P4</td><td style="font-weight: bold;text-align: center;;">Contest 1</td><td style="font-weight: bold;text-align: center;;">Contest 2</td><td style="font-weight: bold;text-align: center;;">Contest 3</td><td style="font-weight: bold;text-align: center;;">Contest 4</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Position Pts</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">Contest 1</td><td style="text-align: center;;">Athlete 2</td><td style="text-align: center;;">2</td><td style="text-align: center;;">4</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Athlete 1</td><td style="text-align: center;;">3</td><td style="text-align: center;;">9</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;">1</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2</td><td style="text-align: center;;">0</td><td style="text-align: right;;"></td><td style="text-align: center;;">54</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">Contest 1</td><td style="text-align: center;;">Athlete 3</td><td style="text-align: center;;">2</td><td style="text-align: center;;">4</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Athlete 2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">14</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">0</td><td style="text-align: right;;"></td><td style="text-align: center;;">80</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">Contest 1</td><td style="text-align: center;;">Athlete 4</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Athlete 3</td><td style="text-align: center;;">3</td><td style="text-align: center;;">9</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: right;;"></td><td style="text-align: center;;">54</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">Contest 2</td><td style="text-align: center;;">Athlete 1</td><td style="text-align: center;;">3</td><td style="text-align: center;;">1</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Athlete 4</td><td style="text-align: center;;">2</td><td style="text-align: center;;">10</td><td style="text-align: center;;">0</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">0</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;">0</td><td style="text-align: right;;"></td><td style="text-align: center;;">57</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">Contest 2</td><td style="text-align: center;;">Athlete 2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">Contest 2</td><td style="text-align: center;;">Athlete 3</td><td style="text-align: center;;">3</td><td style="text-align: center;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">Contest 2</td><td style="text-align: center;;">Athlete 4</td><td style="text-align: center;;">2</td><td style="text-align: center;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">Contest 3</td><td style="text-align: center;;">Athlete 1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">Contest 3</td><td style="text-align: center;;">Athlete 2</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">Contest 3</td><td style="text-align: center;;">Athlete 3</td><td style="text-align: center;;">1</td><td style="text-align: center;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">Contest 3</td><td style="text-align: center;;">Athlete 4</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">****</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G4</th><td style="text-align:left">=RANK(<font color="Blue">R4,$R$4:$R$7,0</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H4</th><td style="text-align:left">{=SUM(<font color="Blue">IF(<font color="Red">B$3:B$14=F4,D$3:D$14</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I4</th><td style="text-align:left">{=SUM(<font color="Blue">IF(<font color="Red">$B$3:$B$14=$F4,IF(<font color="Green">$C$3:$C$14=MID(<font color="Purple">I$3,2,2</font>)*1,1</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J4</th><td style="text-align:left">{=SUM(<font color="Blue">IF(<font color="Red">$B$3:$B$14=$F4,IF(<font color="Green">$C$3:$C$14=MID(<font color="Purple">J$3,2,2</font>)*1,1</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K4</th><td style="text-align:left">{=SUM(<font color="Blue">IF(<font color="Red">$B$3:$B$14=$F4,IF(<font color="Green">$C$3:$C$14=MID(<font color="Purple">K$3,2,2</font>)*1,1</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L4</th><td style="text-align:left">{=SUM(<font color="Blue">IF(<font color="Red">$B$3:$B$14=$F4,IF(<font color="Green">$C$3:$C$14=MID(<font color="Purple">L$3,2,2</font>)*1,1</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M4</th><td style="text-align:left">{=SUM(<font color="Blue">IF(<font color="Red">$B$3:$B$14=$F4,IF(<font color="Green">$A$3:$A$14=M$3,$C$3:$C$14</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N4</th><td style="text-align:left">{=SUM(<font color="Blue">IF(<font color="Red">$B$3:$B$14=$F4,IF(<font color="Green">$A$3:$A$14=N$3,$C$3:$C$14</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">O4</th><td style="text-align:left">{=SUM(<font color="Blue">IF(<font color="Red">$B$3:$B$14=$F4,IF(<font color="Green">$A$3:$A$14=O$3,$C$3:$C$14</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">P4</th><td style="text-align:left">{=SUM(<font color="Blue">IF(<font color="Red">$B$3:$B$14=$F4,IF(<font color="Green">$A$3:$A$14=P$3,$C$3:$C$14</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">R4:R7</th><td style="text-align:left">{=MMULT(<font color="Blue">H4:L7,TRANSPOSE(<font color="Red">COLUMNS(<font color="Green">H4:L4</font>)-(<font color="Green">COLUMN(<font color="Purple">H4:L4</font>)-COLUMN(<font color="Purple">H4</font>)</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz
 
Upvote 0
If you have Excel 2007 or 2010:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;background-color: #C5D9F1;;">Tab - Data</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;background-color: #C5D9F1;;">Tab - Resume</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;text-align: center;;">Contest</td><td style="font-weight: bold;text-align: center;;">Athlete</td><td style="font-weight: bold;text-align: center;;">Pos</td><td style="font-weight: bold;text-align: center;;">Pts</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Overall - position history</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">Contest 1</td><td style="text-align: center;;">Athlete 1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">5</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Athlete</td><td style="font-weight: bold;text-align: center;;">Overall - position</td><td style="font-weight: bold;text-align: center;;">Overall - points</td><td style="font-weight: bold;text-align: center;;">P1</td><td style="font-weight: bold;text-align: center;;">P2</td><td style="font-weight: bold;text-align: center;;">P3</td><td style="font-weight: bold;text-align: center;;">P4</td><td style="font-weight: bold;text-align: center;;">Contest 1</td><td style="font-weight: bold;text-align: center;;">Contest 2</td><td style="font-weight: bold;text-align: center;;">Contest 3</td><td style="font-weight: bold;text-align: center;;">Contest 4</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Position Pts</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">Contest 1</td><td style="text-align: center;;">Athlete 2</td><td style="text-align: center;;">2</td><td style="text-align: center;;">4</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Athlete 1</td><td style="text-align: center;;">3</td><td style="text-align: center;;">9</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;">1</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2</td><td style="text-align: center;;">0</td><td style="text-align: right;;"></td><td style="text-align: center;;">54</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">Contest 1</td><td style="text-align: center;;">Athlete 3</td><td style="text-align: center;;">2</td><td style="text-align: center;;">4</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Athlete 2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">14</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">0</td><td style="text-align: right;;"></td><td style="text-align: center;;">80</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">Contest 1</td><td style="text-align: center;;">Athlete 4</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Athlete 3</td><td style="text-align: center;;">3</td><td style="text-align: center;;">9</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: right;;"></td><td style="text-align: center;;">54</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">Contest 2</td><td style="text-align: center;;">Athlete 1</td><td style="text-align: center;;">3</td><td style="text-align: center;;">1</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Athlete 4</td><td style="text-align: center;;">2</td><td style="text-align: center;;">10</td><td style="text-align: center;;">0</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">0</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;">0</td><td style="text-align: right;;"></td><td style="text-align: center;;">57</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">Contest 2</td><td style="text-align: center;;">Athlete 2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">Contest 2</td><td style="text-align: center;;">Athlete 3</td><td style="text-align: center;;">3</td><td style="text-align: center;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">Contest 2</td><td style="text-align: center;;">Athlete 4</td><td style="text-align: center;;">2</td><td style="text-align: center;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">Contest 3</td><td style="text-align: center;;">Athlete 1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">Contest 3</td><td style="text-align: center;;">Athlete 2</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">Contest 3</td><td style="text-align: center;;">Athlete 3</td><td style="text-align: center;;">1</td><td style="text-align: center;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">Contest 3</td><td style="text-align: center;;">Athlete 4</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">****</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G4</th><td style="text-align:left">=RANK(<font color="Blue">R4,R$4:R$7,0</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H4</th><td style="text-align:left">=SUMIF(<font color="Blue">B$3:B$14,F4,D$3:D$14</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I4</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$B$3:$B$14=$F4</font>),--(<font color="Red">$C$3:$C$14=MID(<font color="Green">I$3,2,2</font>)*1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J4</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$B$3:$B$14=$F4</font>),--(<font color="Red">$C$3:$C$14=MID(<font color="Green">J$3,2,2</font>)*1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K4</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$B$3:$B$14=$F4</font>),--(<font color="Red">$C$3:$C$14=MID(<font color="Green">K$3,2,2</font>)*1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L4</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">$B$3:$B$14=$F4</font>),--(<font color="Red">$C$3:$C$14=MID(<font color="Green">L$3,2,2</font>)*1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M4</th><td style="text-align:left">=SUMIFS(<font color="Blue">$C$3:$C$14,$B$3:$B$14,$F4,$A$3:$A$14,M$3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N4</th><td style="text-align:left">=SUMIFS(<font color="Blue">$C$3:$C$14,$B$3:$B$14,$F4,$A$3:$A$14,N$3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">O4</th><td style="text-align:left">=SUMIFS(<font color="Blue">$C$3:$C$14,$B$3:$B$14,$F4,$A$3:$A$14,O$3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">P4</th><td style="text-align:left">=SUMIFS(<font color="Blue">$C$3:$C$14,$B$3:$B$14,$F4,$A$3:$A$14,P$3</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">R4:R7</th><td style="text-align:left">{=MMULT(<font color="Blue">H4:L7,TRANSPOSE(<font color="Red">COLUMNS(<font color="Green">H4:L4</font>)-(<font color="Green">COLUMN(<font color="Purple">H4:L4</font>)-COLUMN(<font color="Purple">H4</font>)</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz
 
Upvote 0
Another way (with your layout):

Note: array formula in C11, D11 and E11 - entered with Ctrl+Shift+Enter and not only Enter.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Contest 1 - position</td><td style="font-weight: bold;text-align: center;;">Contest 1 - points</td><td style="font-weight: bold;text-align: center;;">Contest 2 - position</td><td style="font-weight: bold;text-align: center;;">Contest 2 - points</td><td style="font-weight: bold;text-align: center;;">Contest 3 - position</td><td style="font-weight: bold;text-align: center;;">Contest 3 - points</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;text-align: center;;">Athlete 1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">5</td><td style="text-align: center;;">3</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;text-align: center;;">Athlete 2</td><td style="text-align: center;;">2</td><td style="text-align: center;;">4</td><td style="text-align: center;;">1</td><td style="text-align: center;;">7</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;text-align: center;;">Athlete 3</td><td style="text-align: center;;">2</td><td style="text-align: center;;">4</td><td style="text-align: center;;">3</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;text-align: center;;">Athlete 4</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2</td><td style="text-align: center;;">2</td><td style="text-align: center;;">6</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Overall - position</td><td style="font-weight: bold;text-align: center;;">Overall - points</td><td style="font-weight: bold;text-align: center;;">Overall - position history</td><td style="font-weight: bold;text-align: center;;">Position Pts</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="font-weight: bold;text-align: center;;">Athlete 1</td><td style="text-align: center;;">3</td><td style="text-align: center;;">9</td><td style="text-align: center;;">1 P1, 1 P2, 1 P3</td><td style="text-align: center;;">79</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="font-weight: bold;text-align: center;;">Athlete 2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">14</td><td style="text-align: center;;">1 P1, 2 P2</td><td style="text-align: center;;">120</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="font-weight: bold;text-align: center;;">Athlete 3</td><td style="text-align: center;;">3</td><td style="text-align: center;;">9</td><td style="text-align: center;;">1 P1, 1 P2, 1 P3</td><td style="text-align: center;;">79</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="font-weight: bold;text-align: center;;">Athlete 4</td><td style="text-align: center;;">2</td><td style="text-align: center;;">10</td><td style="text-align: center;;">1 P2, 2 P3</td><td style="text-align: center;;">84</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet01</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B11</th><td style="text-align:left">=RANK(<font color="Blue">E11,E$11:E$14,0</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C11</th><td style="text-align:left">{=SUM(<font color="Blue">$B3:$G3*MOD(<font color="Red">COLUMN(<font color="Green">$B3:$G3</font>)-COLUMN(<font color="Green">$B3</font>),2</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D11</th><td style="text-align:left">{=LEFT(<font color="Blue">
IF(<font color="Red">SUM(<font color="Green">CHOOSE(<font color="Purple">$B3:$G3*MOD(<font color="Teal">COLUMN(<font color="#FF00FF">$B3:$G3</font>)-COLUMN(<font color="#FF00FF">$B3</font>)+1,2</font>)+1,0,1,0,0</font>)</font>),SUM(<font color="Green">CHOOSE(<font color="Purple">$B3:$G3*MOD(<font color="Teal">COLUMN(<font color="#FF00FF">$B3:$G3</font>)-COLUMN(<font color="#FF00FF">$B3</font>)+1,2</font>)+1,0,1,0,0</font>)</font>)&" P1, ",""</font>)&
IF(<font color="Red">SUM(<font color="Green">CHOOSE(<font color="Purple">$B3:$G3*MOD(<font color="Teal">COLUMN(<font color="#FF00FF">$B3:$G3</font>)-COLUMN(<font color="#FF00FF">$B3</font>)+1,2</font>)+1,0,0,1,0</font>)</font>),SUM(<font color="Green">CHOOSE(<font color="Purple">$B3:$G3*MOD(<font color="Teal">COLUMN(<font color="#FF00FF">$B3:$G3</font>)-COLUMN(<font color="#FF00FF">$B3</font>)+1,2</font>)+1,0,0,1,0</font>)</font>)&" P2, ",""</font>)&
IF(<font color="Red">SUM(<font color="Green">CHOOSE(<font color="Purple">$B3:$G3*MOD(<font color="Teal">COLUMN(<font color="#FF00FF">$B3:$G3</font>)-COLUMN(<font color="#FF00FF">$B3</font>)+1,2</font>)+1,0,0,0,1</font>)</font>),SUM(<font color="Green">CHOOSE(<font color="Purple">$B3:$G3*MOD(<font color="Teal">COLUMN(<font color="#FF00FF">$B3:$G3</font>)-COLUMN(<font color="#FF00FF">$B3</font>)+1,2</font>)+1,0,0,0,1</font>)</font>)&" P3,",""</font>),
LEN(<font color="Red">
IF(<font color="Green">SUM(<font color="Purple">CHOOSE(<font color="Teal">$B3:$G3*MOD(<font color="#FF00FF">COLUMN(<font color="Navy">$B3:$G3</font>)-COLUMN(<font color="Navy">$B3</font>)+1,2</font>)+1,0,1,0,0</font>)</font>),SUM(<font color="Purple">CHOOSE(<font color="Teal">$B3:$G3*MOD(<font color="#FF00FF">COLUMN(<font color="Navy">$B3:$G3</font>)-COLUMN(<font color="Navy">$B3</font>)+1,2</font>)+1,0,1,0,0</font>)</font>)&" P1, ",""</font>)&
IF(<font color="Green">SUM(<font color="Purple">CHOOSE(<font color="Teal">$B3:$G3*MOD(<font color="#FF00FF">COLUMN(<font color="Navy">$B3:$G3</font>)-COLUMN(<font color="Navy">$B3</font>)+1,2</font>)+1,0,0,1,0</font>)</font>),SUM(<font color="Purple">CHOOSE(<font color="Teal">$B3:$G3*MOD(<font color="#FF00FF">COLUMN(<font color="Navy">$B3:$G3</font>)-COLUMN(<font color="Navy">$B3</font>)+1,2</font>)+1,0,0,1,0</font>)</font>)&" P2, ",""</font>)&
IF(<font color="Green">SUM(<font color="Purple">CHOOSE(<font color="Teal">$B3:$G3*MOD(<font color="#FF00FF">COLUMN(<font color="Navy">$B3:$G3</font>)-COLUMN(<font color="Navy">$B3</font>)+1,2</font>)+1,0,0,0,1</font>)</font>),SUM(<font color="Purple">CHOOSE(<font color="Teal">$B3:$G3*MOD(<font color="#FF00FF">COLUMN(<font color="Navy">$B3:$G3</font>)-COLUMN(<font color="Navy">$B3</font>)+1,2</font>)+1,0,0,0,1</font>)</font>)&" P3, ",""</font>)</font>)-2</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E11</th><td style="text-align:left">{=SUM(<font color="Blue">(<font color="Red">$B3:$G3*MOD(<font color="Green">COLUMN(<font color="Purple">$B3:$G3</font>)-COLUMN(<font color="Purple">$B3</font>),2</font>)</font>)*8+CHOOSE(<font color="Red">$B3:$G3*MOD(<font color="Green">COLUMN(<font color="Purple">$B3:$G3</font>)-COLUMN(<font color="Purple">$B3</font>)+1,2</font>)+1,0,4,2,1</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz
 
Upvote 0
Thanks a lot Markmzz !!

I was just thinking that an unbelievably easy way to sort the contestants would be to just create a generic quantifier. For example, for a maximum of 40 contestants that will occupy no more than 10 different positions at each contest due to similar results, I can just use the position they occupy at the end of the contest and multiply it by a power of 10 for the overall char and add to that the overall sum of points multiplied by a greater power of 10 so this way sorting the contestants become a simple descending order sort. My problem is that I don't know how to simplify the formula.

<table style="width: 494px; height: 92px;" frame="VOID" rules="NONE" border="0" cellspacing="0" cols="8"><colgroup><col width="131"><col width="89"><col width="37"><col width="37"><col width="37"><col width="37"><col width="37"><col width="37"></colgroup><tbody><tr><td align="center" height="17" width="131">
</td><td align="center" width="89">A</td><td align="center" width="37">B</td><td align="center" width="37">C</td><td align="center" width="37">D</td><td align="center" width="37">E</td><td align="center" width="37">F</td><td align="center" width="37">G</td></tr><tr><td align="center" height="18">1</td><td align="center">Contestant</td><td align="center">Position pts</td><td align="center">Position Contest 1</td><td align="center">Position Contest 2</td><td align="center">Position Contest 3</td><td align="center">Position Contest 4</td><td align="center">Position Contest 5</td></tr><tr><td align="center" height="17">2</td><td align="center">Mike</td><td align="center"> <table frame="VOID" rules="NONE" border="0" cellspacing="0" cols="1"><colgroup><col width="84"></colgroup><tbody><tr><td align="RIGHT" height="17" width="84">2.40E+016</td></tr></tbody> </table> </td><td align="center">1</td><td align="center">3</td><td align="center">5</td><td align="center">3</td><td align="center">1</td></tr></tbody> </table>

<table style="width: 495px; height: 90px;" frame="VOID" rules="NONE" border="0" cellspacing="0" cols="8"><colgroup><col width="37"><col width="44"><col width="44"><col width="44"><col width="44"><col width="44"><col width="44"><col width="44"></colgroup><tbody><tr><td align="center" height="17" width="37">
</td><td align="center" width="44">A</td><td align="center" width="44">B</td><td align="center" width="44">C</td><td align="center" width="44">D</td><td align="center" width="44">E</td><td align="center" width="44">F</td><td align="center" width="44">G</td></tr><tr><td align="center" height="18">51</td><td align="center">Contestant</td><td align="center">Contestant pts</td><td align="center">Pts Contest 1</td><td align="center">Pts Contest 2</td><td align="center">Pts Contest 3</td><td align="center">Pts Contest 4</td><td align="center">Pts Contest 5</td></tr><tr><td align="center" height="17">52</td><td align="center">Mike</td><td align="center">24</td><td align="center">7</td><td align="center">5</td><td align="center">1</td><td align="center">3</td><td align="center">8</td></tr></tbody> </table>

B2=B52*POWER(10;15)+POWER(10;11-B2)+POWER(10;11-C2)+POWER(10;11-D2)+POWER(10;11-E2)+POWER(10;11-F2)

Unfortunately that sum will have 35 items (1 for each contest plus one for the overall score) and I have no idea how to simplify it.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
smide
  • Solved
Formula for percentile if data are in multiple columns
Replies
2
Views
123
hagia_sofia
hagia_sofia
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
117
Peter_SSs
Peter_SSs
M
  • Question Question
SLA Time Calculation
Replies
5
Views
355
felixstraube
F
E
  • Question Question
VBA code clean up
Replies
9
Views
582
mumps
mumps

Forum statistics

Threads
1,224,585
Messages
6,179,700
Members
452,938
Latest member
babeneker

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top