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 !
 
Sort by date Sort by votes
gn82 said:
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" border=0 rules=none cellSpacing=0 frame=void 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 height=17 width=131 align=center>


</TD><TD width=89 align=center>A</TD><TD width=37 align=center>B</TD><TD width=37 align=center>C</TD><TD width=37 align=center>D</TD><TD width=37 align=center>E</TD><TD width=37 align=center>F</TD><TD width=37 align=center>G</TD></TR><TR><TD height=18 align=center>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 height=17 align=center>2</TD><TD align=center>Mike</TD><TD align=center><TABLE border=0 rules=none cellSpacing=0 frame=void cols=1><COLGROUP><COL width=84></COLGROUP><TBODY><TR><TD height=17 width=84 align=right>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" border=0 rules=none cellSpacing=0 frame=void 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 height=17 width=37 align=center>


</TD><TD width=44 align=center>A</TD><TD width=44 align=center>B</TD><TD width=44 align=center>C</TD><TD width=44 align=center>D</TD><TD width=44 align=center>E</TD><TD width=44 align=center>F</TD><TD width=44 align=center>G</TD></TR><TR><TD height=18 align=center>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 height=17 align=center>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.
Click to expand...


Maybe this:

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; 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="TEXT-ALIGN: center; COLOR: #161120">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><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</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="TEXT-ALIGN: center; COLOR: #161120">18</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="TEXT-ALIGN: center; COLOR: #161120">19</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="TEXT-ALIGN: center; COLOR: #161120">20</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="TEXT-ALIGN: center; COLOR: #161120">21</TD><TD>Contestant</TD><TD>Position pts</TD><TD>Position Contest 1</TD><TD>Position Contest 2</TD><TD>Position Contest 3</TD><TD>Position Contest 4</TD><TD>Position Contest 5</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">22</TD><TD>Mike</TD><TD>2.4E+16</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">23</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="TEXT-ALIGN: center; COLOR: #161120">24</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="TEXT-ALIGN: center; COLOR: #161120">25</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="TEXT-ALIGN: center; COLOR: #161120">26</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="TEXT-ALIGN: center; COLOR: #161120">27</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="TEXT-ALIGN: center; COLOR: #161120">28</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="TEXT-ALIGN: center; COLOR: #161120">29</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="TEXT-ALIGN: center; COLOR: #161120">30</TD><TD>Contestant</TD><TD>Contestant pts</TD><TD>Pts Contest 1</TD><TD>Pts Contest 2</TD><TD>Pts Contest 3</TD><TD>Pts Contest 4</TD><TD>Pts Contest 5</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">31</TD><TD>Mike</TD><TD style="TEXT-ALIGN: right">24</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">8</TD></TR></TBODY></TABLE>Sheet02


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B22</TH><TD style="TEXT-ALIGN: left">=B31*POWER(10,15)+SUMPRODUCT(POWER(10,11-C22:G22))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Markmzz
 
Last edited:
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Another example:

<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 /></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>AF</th><th>AG</th><th>AH</th><th>AI</th><th>AJ</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Contestant</td><td style="text-align: center;;">Position pts</td><td style="text-align: center;;">Position Contest 1</td><td style="text-align: center;;">Position Contest 2</td><td style="text-align: center;;">Position Contest 3</td><td style="text-align: center;;">Position Contest 4</td><td style="text-align: center;;">Position Contest 5</td><td style="text-align: center;;">Position Contest 30</td><td style="text-align: center;;">Position Contest 31</td><td style="text-align: center;;">Position Contest 32</td><td style="text-align: center;;">Position Contest 33</td><td style="text-align: center;;">Position Contest 34</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">Mike</td><td style="text-align: center;;">1.76042E+13</td><td style="text-align: center;;">6</td><td style="text-align: center;;">7</td><td style="text-align: center;;">10</td><td style="text-align: center;;">5</td><td style="text-align: center;;">9</td><td style="text-align: center;;">9</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;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">Mark</td><td style="text-align: center;;">2.12101E+13</td><td style="text-align: center;;">6</td><td style="text-align: center;;">3</td><td style="text-align: center;;">1</td><td style="text-align: center;;">7</td><td style="text-align: center;;">1</td><td style="text-align: center;;">6</td><td style="text-align: center;;">4</td><td style="text-align: center;;">8</td><td style="text-align: center;;">3</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">4</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><tr ><td style="color: #161120;text-align: center;">49</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><tr ><td style="color: #161120;text-align: center;">50</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><tr ><td style="color: #161120;text-align: center;">51</td><td style="text-align: center;;">Contestant</td><td style="text-align: center;;">Contestant pts</td><td style="text-align: center;;">Pts Contest 1</td><td style="text-align: center;;">Pts Contest 2</td><td style="text-align: center;;">Pts Contest 3</td><td style="text-align: center;;">Pts Contest 4</td><td style="text-align: center;;">Pts Contest 5</td><td style="text-align: center;;">Pts Contest 30</td><td style="text-align: center;;">Pts Contest 31</td><td style="text-align: center;;">Pts Contest 32</td><td style="text-align: center;;">Pts Contest 33</td><td style="text-align: center;;">Pts Contest 34</td></tr><tr ><td style="color: #161120;text-align: center;">52</td><td style="text-align: center;;">Mike</td><td style="text-align: center;;">176</td><td style="text-align: center;;">5</td><td style="text-align: center;;">4</td><td style="text-align: center;;">1</td><td style="text-align: center;;">6</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;;">8</td><td style="text-align: center;;">10</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">53</td><td style="text-align: center;;">Mark</td><td style="text-align: center;;">212</td><td style="text-align: center;;">5</td><td style="text-align: center;;">8</td><td style="text-align: center;;">10</td><td style="text-align: center;;">4</td><td style="text-align: center;;">10</td><td style="text-align: center;;">5</td><td style="text-align: center;;">7</td><td style="text-align: center;;">3</td><td style="text-align: center;;">8</td><td style="text-align: center;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">54</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><tr ><td style="color: #161120;text-align: center;">55</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><tr ><td style="color: #161120;text-align: center;">56</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><tr ><td style="color: #161120;text-align: center;">57</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: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">Sheet03</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">B2</th><td style="text-align:left">=B52*POWER(<font color="Blue">10,11</font>)+SUMPRODUCT(<font color="Blue">POWER(<font color="Red">10,10-C2:AJ2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B52</th><td style="text-align:left">=SUM(<font color="Blue">C52:AJ52</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz
 
Upvote 0
@ markmzz - Thanks once more for all the help!

Is there an easy way to sort contestants based on their score (descending) now that the B2=B52*POWER(10,11)+SUMPRODUCT(POWER(10,10-C2:AJ2)) formula allows for an easy way to quantify all variables?

<table border="0" cellpadding="0" cellspacing="0" width="346"><col style="width: 38pt;" span="4" width="50"> <col style="width: 110pt;" width="146"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 38pt;" align="center" height="17" width="50">#</td> <td style="width: 38pt;" align="center" width="50">Contestant</td> <td style="width: 38pt;" align="center" width="50">Contest_pts</td> <td style="width: 38pt;" align="center" width="50">Overall_pos_history</td> <td class="xl24" style="width: 110pt;" align="center" width="146">Position_pts</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="center" height="17">1</td> <td align="center">Mark</td> <td align="center">212</td> <td class="xl25" align="center">1 P3, 1 P6</td> <td class="xl25" align="center">2.12E+13</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="center" height="17">2</td> <td align="center">Mike</td> <td align="center">176</td> <td class="xl24" align="center">1 P6, 1 P7</td> <td class="xl25" align="center">1.76E+13</td></tr></tbody></table>
<table border="0" cellpadding="0" cellspacing="0" width="346"><tbody><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt; width: 38pt;" align="center" height="17" width="50">
</td><td style="width: 38pt;" align="center" width="50">
</td><td style="width: 38pt;" align="center" width="50">
</td><td style="width: 38pt;" align="center" width="50">
</td><td class="xl24" style="width: 110pt;" align="center" width="146">
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" align="center" height="17">
</td><td align="center">
</td><td align="center">
</td><td class="xl24" align="center">
</td><td class="xl25" align="center">
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" align="center" height="17">
</td><td align="center">
</td><td align="center">
</td><td class="xl25" align="center">
</td><td class="xl25" align="center">
</td></tr> </tbody></table>
 
Last edited:
Upvote 0
gn82 said:
@ markmzz - Thanks once more for all the help!

Is there an easy way to sort contestants based on their score (descending) now that the B2=B52*POWER(10,11)+SUMPRODUCT(POWER(10,10-C2:AJ2)) formula allows for an easy way to quantify all variables?

<TABLE border=0 cellSpacing=0 cellPadding=0 width=346><COLGROUP><COL style="WIDTH: 38pt" span=4 width=50><COL style="WIDTH: 110pt" width=146><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="WIDTH: 38pt; HEIGHT: 12.75pt" height=17 width=50 align=center>#</TD><TD style="WIDTH: 38pt" width=50 align=center>Contestant</TD><TD style="WIDTH: 38pt" width=50 align=center>Contest_pts</TD><TD style="WIDTH: 38pt" width=50 align=center>Overall_pos_history</TD><TD style="WIDTH: 110pt" class=xl24 width=146 align=center>Position_pts</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17 align=center>1</TD><TD align=center>Mark</TD><TD align=center>212</TD><TD class=xl25 align=center>1 P3, 1 P6</TD><TD class=xl25 align=center>2.12E+13</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17 align=center>2</TD><TD align=center>Mike</TD><TD align=center>176</TD><TD class=xl24 align=center>1 P6, 1 P7</TD><TD class=xl25 align=center>1.76E+13</TD></TR></TBODY></TABLE>
Click to expand...

Maybe this:

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>AJ</TH><TH>AK</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center">Contestant</TD><TD style="TEXT-ALIGN: center">Position pts</TD><TD style="TEXT-ALIGN: center">Position Contest 1</TD><TD style="TEXT-ALIGN: center">Position Contest 2</TD><TD style="TEXT-ALIGN: center">Position Contest 3</TD><TD style="TEXT-ALIGN: center">Position Contest 34</TD><TD style="TEXT-ALIGN: center">Overall_pos_history</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: center">Mike</TD><TD style="TEXT-ALIGN: center">1.76042E+13</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">3 P1, 3 P2, 7 P3, 3 P4, 3 P5, 4 P6, 3 P7, 2 P8, 2 P9, 4 P10</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center">Mark</TD><TD style="TEXT-ALIGN: center">2.12101E+13</TD><TD style="TEXT-ALIGN: center">6</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 P1, 3 P3, 5 P4, 4 P5, 2 P6, 2 P7, 3 P8, 1 P9, 10 P10</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</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: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">49</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: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">50</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: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">51</TD><TD style="TEXT-ALIGN: center">Contestant</TD><TD style="TEXT-ALIGN: center">Contest_pts</TD><TD style="TEXT-ALIGN: center">Pts Contest 1</TD><TD style="TEXT-ALIGN: center">Pts Contest 2</TD><TD style="TEXT-ALIGN: center">Pts Contest 3</TD><TD style="TEXT-ALIGN: center">Pts Contest 34</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">52</TD><TD style="TEXT-ALIGN: center">Mike</TD><TD style="TEXT-ALIGN: center">176</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">53</TD><TD style="TEXT-ALIGN: center">Mark</TD><TD style="TEXT-ALIGN: center">212</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">54</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: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">55</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: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">56</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: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">57</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="TEXT-ALIGN: center; COLOR: #161120">58</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="TEXT-ALIGN: center; COLOR: #161120">59</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: center; COLOR: #ffffff"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">60</TD><TD style="TEXT-ALIGN: right"></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="TEXT-ALIGN: center; COLOR: #161120">61</TD><TD style="TEXT-ALIGN: center">#</TD><TD style="TEXT-ALIGN: center">Contestant</TD><TD style="TEXT-ALIGN: center">Contest_pts</TD><TD style="TEXT-ALIGN: center">Overall_pos_history</TD><TD style="TEXT-ALIGN: center">Position_pts</TD><TD style="TEXT-ALIGN: center; COLOR: #ffffff">#VALOR!</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">62</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">Mark</TD><TD style="TEXT-ALIGN: center">212</TD><TD style="TEXT-ALIGN: center">4 P1, 3 P3, 5 P4, 4 P5, 2 P6, 2 P7, 3 P8, 1 P9, 10 P10</TD><TD style="TEXT-ALIGN: center">2.12101E+13</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">63</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">Mike</TD><TD style="TEXT-ALIGN: center">176</TD><TD style="TEXT-ALIGN: center">3 P1, 3 P2, 7 P3, 3 P4, 3 P5, 4 P6, 3 P7, 2 P8, 2 P9, 4 P10</TD><TD style="TEXT-ALIGN: center">1.76042E+13</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>Sheet04


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>A62</TH><TD style="TEXT-ALIGN: left">=RANK(E62,$E$62:$E$63)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>AJ61</TH><TD style="TEXT-ALIGN: left">=Contestant+Contest_pts+Overall_pos_history+Position_pts</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>AK2</TH><TD style="TEXT-ALIGN: left">{=LEFT(IF(SUM(CHOOSE($C52:$AJ52,1,0,0,0,0,0,0,0,0,0)),SUM(CHOOSE($C52:$AJ52,1,0,0,0,0,0,0,0,0,0))&" P1, ","")&
IF(SUM(CHOOSE($C52:$AJ52,0,1,0,0,0,0,0,0,0,0)),SUM(CHOOSE($C52:$AJ52,0,1,0,0,0,0,0,0,0,0))&" P2, ","")&
IF(SUM(CHOOSE($C52:$AJ52,0,0,1,0,0,0,0,0,0,0)),SUM(CHOOSE($C52:$AJ52,0,0,1,0,0,0,0,0,0,0))&" P3, ","")&
IF(SUM(CHOOSE($C52:$AJ52,0,0,0,1,0,0,0,0,0,0)),SUM(CHOOSE($C52:$AJ52,0,0,0,1,0,0,0,0,0,0))&" P4, ","")&
IF(SUM(CHOOSE($C52:$AJ52,0,0,0,0,1,0,0,0,0,0)),SUM(CHOOSE($C52:$AJ52,0,0,0,0,1,0,0,0,0,0))&" P5, ","")&
IF(SUM(CHOOSE($C52:$AJ52,0,0,0,0,0,1,0,0,0,0)),SUM(CHOOSE($C52:$AJ52,0,0,0,0,0,1,0,0,0,0))&" P6, ","")&
IF(SUM(CHOOSE($C52:$AJ52,0,0,0,0,0,0,1,0,0,0)),SUM(CHOOSE($C52:$AJ52,0,0,0,0,0,0,1,0,0,0))&" P7, ","")&
IF(SUM(CHOOSE($C52:$AJ52,0,0,0,0,0,0,0,1,0,0)),SUM(CHOOSE($C52:$AJ52,0,0,0,0,0,0,0,1,0,0))&" P8, ","")&
IF(SUM(CHOOSE($C52:$AJ52,0,0,0,0,0,0,0,0,1,0)),SUM(CHOOSE($C52:$AJ52,0,0,0,0,0,0,0,0,1,0))&" P9, ","")&
IF(SUM(CHOOSE($C52:$AJ52,0,0,0,0,0,0,0,0,0,1)),SUM(CHOOSE($C52:$AJ52,0,0,0,0,0,0,0,0,0,1))&" P10, ",""),LEN(
IF(SUM(CHOOSE($C52:$AJ52,1,0,0,0,0,0,0,0,0,0)),SUM(CHOOSE($C52:$AJ52,1,0,0,0,0,0,0,0,0,0))&" P1, ","")&
IF(SUM(CHOOSE($C52:$AJ52,0,1,0,0,0,0,0,0,0,0)),SUM(CHOOSE($C52:$AJ52,0,1,0,0,0,0,0,0,0,0))&" P2, ","")&
IF(SUM(CHOOSE($C52:$AJ52,0,0,1,0,0,0,0,0,0,0)),SUM(CHOOSE($C52:$AJ52,0,0,1,0,0,0,0,0,0,0))&" P3, ","")&
IF(SUM(CHOOSE($C52:$AJ52,0,0,0,1,0,0,0,0,0,0)),SUM(CHOOSE($C52:$AJ52,0,0,0,1,0,0,0,0,0,0))&" P4, ","")&
IF(SUM(CHOOSE($C52:$AJ52,0,0,0,0,1,0,0,0,0,0)),SUM(CHOOSE($C52:$AJ52,0,0,0,0,1,0,0,0,0,0))&" P5, ","")&
IF(SUM(CHOOSE($C52:$AJ52,0,0,0,0,0,1,0,0,0,0)),SUM(CHOOSE($C52:$AJ52,0,0,0,0,0,1,0,0,0,0))&" P6, ","")&
IF(SUM(CHOOSE($C52:$AJ52,0,0,0,0,0,0,1,0,0,0)),SUM(CHOOSE($C52:$AJ52,0,0,0,0,0,0,1,0,0,0))&" P7, ","")&
IF(SUM(CHOOSE($C52:$AJ52,0,0,0,0,0,0,0,1,0,0)),SUM(CHOOSE($C52:$AJ52,0,0,0,0,0,0,0,1,0,0))&" P8, ","")&
IF(SUM(CHOOSE($C52:$AJ52,0,0,0,0,0,0,0,0,1,0)),SUM(CHOOSE($C52:$AJ52,0,0,0,0,0,0,0,0,1,0))&" P9, ","")&
IF(SUM(CHOOSE($C52:$AJ52,0,0,0,0,0,0,0,0,0,1)),SUM(CHOOSE($C52:$AJ52,0,0,0,0,0,0,0,0,0,1))&" P10, ",""))-2)}
</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B62</TH><TD style="TEXT-ALIGN: left">{=INDEX(INDIRECT(B$61),MIN(IF(LARGE($B$2:$B$3-ROW($B$2:$B$3)/10^6,ROWS(B$62:B62))=$B$2:$B$3-ROW($B$2:$B$3)/10^6,ROW($A$2:$A$3)-ROW($A$2)+1)))}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C62</TH><TD style="TEXT-ALIGN: left">{=INDEX(INDIRECT(C$61),MIN(IF(LARGE($B$2:$B$3-ROW($B$2:$B$3)/10^6,ROWS(C$62:C62))=$B$2:$B$3-ROW($B$2:$B$3)/10^6,ROW($A$2:$A$3)-ROW($A$2)+1)))}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>D62</TH><TD style="TEXT-ALIGN: left">{=INDEX(INDIRECT(D$61),MIN(IF(LARGE($B$2:$B$3-ROW($B$2:$B$3)/10^6,ROWS(D$62:D62))=$B$2:$B$3-ROW($B$2:$B$3)/10^6,ROW($A$2:$A$3)-ROW($A$2)+1)))}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>E62</TH><TD style="TEXT-ALIGN: left">{=INDEX(INDIRECT(E$61),MIN(IF(LARGE($B$2:$B$3-ROW($B$2:$B$3)/10^6,ROWS(E$62:E62))=$B$2:$B$3-ROW($B$2:$B$3)/10^6,ROW($A$2:$A$3)-ROW($A$2)+1)))}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Workbook Defined Names<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Name</TH><TH style="TEXT-ALIGN: left">Refers To</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>Contest_pts</TH><TD style="TEXT-ALIGN: left">=Sheet04!$B$52:$B$53</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>Contestant</TH><TD style="TEXT-ALIGN: left">=Sheet04!$A$2:$A$3</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>Overall_pos_history</TH><TD style="TEXT-ALIGN: left">=Sheet04!$AK$2:$AK$3</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>Position_pts</TH><TD style="TEXT-ALIGN: left">=Sheet04!$B$2:$B$3</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Markmzz
 
Last edited:
Upvote 0
Everything works superbly except for the AK2 formula where Excel says "formula is too long" :(
 
Upvote 0
gn82 said:
Everything works superbly except for the AK2 formula where Excel says "formula is too long" :(
Click to expand...

Try this:

Note1: you have one formula (AK2) and one function (AL2), choose one.

Note2: the formula in AK2 of my previous post isn't ok.

<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>AK</th><th>AL</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Contestant</td><td style="text-align: center;;">Position pts</td><td style="text-align: center;;">Position Contest 1</td><td style="text-align: center;;">Position Contest 2</td><td style="text-align: center;;">Position Contest 3</td><td style="text-align: center;;">Overall_pos_history</td><td style="text-align: center;;">Overall_pos_history</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">Mike</td><td style="text-align: center;;">1.76042E+13</td><td style="text-align: center;;">6</td><td style="text-align: center;;">7</td><td style="text-align: center;;">10</td><td style="text-align: center;;">4 P1, 2 P2, 2 P3, 3 P4, 4 P5, 3 P6, 3 P7, 7 P8, 3 P9, 3 P10</td><td style="text-align: center;;">4 P1, 2 P2, 2 P3, 3 P4, 4 P5, 3 P6, 3 P7, 7 P8, 3 P9, 3 P10</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">Mark</td><td style="text-align: center;;">2.12101E+13</td><td style="text-align: center;;">6</td><td style="text-align: center;;">3</td><td style="text-align: center;;">1</td><td style="text-align: center;;">10 P1, 1 P2, 3 P3, 2 P4, 2 P5, 4 P6, 5 P7, 3 P8, 4 P10</td><td style="text-align: center;;">10 P1, 1 P2, 3 P3, 2 P4, 2 P5, 4 P6, 5 P7, 3 P8, 4 P10</td></tr><tr ><td style="color: #161120;text-align: center;">4</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></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">Sheet04</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">B2</th><td style="text-align:left">=B52*POWER(<font color="Blue">10,11</font>)+SUMPRODUCT(<font color="Blue">POWER(<font color="Red">10,10-C2:AJ2</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">AK2</th><td style="text-align:left">=LEFT(<font color="Blue">
IF(<font color="Red">COUNTIF(<font color="Green">C2:AJ2,1</font>),COUNTIF(<font color="Green">C2:AJ2,1</font>)&" P1, ",""</font>)&IF(<font color="Red">COUNTIF(<font color="Green">C2:AJ2,2</font>),COUNTIF(<font color="Green">C2:AJ2,2</font>)&" P2, ",""</font>)&IF(<font color="Red">COUNTIF(<font color="Green">C2:AJ2,3</font>),COUNTIF(<font color="Green">C2:AJ2,3</font>)&" P3, ",""</font>)&
IF(<font color="Red">COUNTIF(<font color="Green">C2:AJ2,4</font>),COUNTIF(<font color="Green">C2:AJ2,4</font>)&" P4, ",""</font>)&IF(<font color="Red">COUNTIF(<font color="Green">C2:AJ2,5</font>),COUNTIF(<font color="Green">C2:AJ2,5</font>)&" P5, ",""</font>)&IF(<font color="Red">COUNTIF(<font color="Green">C2:AJ2,6</font>),COUNTIF(<font color="Green">C2:AJ2,6</font>)&" P6, ",""</font>)&
IF(<font color="Red">COUNTIF(<font color="Green">C2:AJ2,7</font>),COUNTIF(<font color="Green">C2:AJ2,7</font>)&" P7, ",""</font>)&IF(<font color="Red">COUNTIF(<font color="Green">C2:AJ2,8</font>),COUNTIF(<font color="Green">C2:AJ2,8</font>)&" P8, ",""</font>)&IF(<font color="Red">COUNTIF(<font color="Green">C2:AJ2,9</font>),COUNTIF(<font color="Green">C2:AJ2,9</font>)&" P9, ",""</font>)&
IF(<font color="Red">COUNTIF(<font color="Green">C2:AJ2,10</font>),COUNTIF(<font color="Green">C2:AJ2,10</font>)&" P10, ",""</font>),
LEN(<font color="Red">
IF(<font color="Green">COUNTIF(<font color="Purple">C2:AJ2,1</font>),COUNTIF(<font color="Purple">C2:AJ2,1</font>)&" P1, ",""</font>)&IF(<font color="Green">COUNTIF(<font color="Purple">C2:AJ2,2</font>),COUNTIF(<font color="Purple">C2:AJ2,2</font>)&" P2, ",""</font>)&IF(<font color="Green">COUNTIF(<font color="Purple">C2:AJ2,3</font>),COUNTIF(<font color="Purple">C2:AJ2,3</font>)&" P3, ",""</font>)&
IF(<font color="Green">COUNTIF(<font color="Purple">C2:AJ2,4</font>),COUNTIF(<font color="Purple">C2:AJ2,4</font>)&" P4, ",""</font>)&IF(<font color="Green">COUNTIF(<font color="Purple">C2:AJ2,5</font>),COUNTIF(<font color="Purple">C2:AJ2,5</font>)&" P5, ",""</font>)&IF(<font color="Green">COUNTIF(<font color="Purple">C2:AJ2,6</font>),COUNTIF(<font color="Purple">C2:AJ2,6</font>)&" P6, ",""</font>)&
IF(<font color="Green">COUNTIF(<font color="Purple">C2:AJ2,7</font>),COUNTIF(<font color="Purple">C2:AJ2,7</font>)&" P7, ",""</font>)&IF(<font color="Green">COUNTIF(<font color="Purple">C2:AJ2,8</font>),COUNTIF(<font color="Purple">C2:AJ2,8</font>)&" P8, ",""</font>)&IF(<font color="Green">COUNTIF(<font color="Purple">C2:AJ2,9</font>),COUNTIF(<font color="Purple">C2:AJ2,9</font>)&" P9, ",""</font>)&
IF(<font color="Green">COUNTIF(<font color="Purple">C2:AJ2,10</font>),COUNTIF(<font color="Purple">C2:AJ2,10</font>)&" P10, ",""</font>)</font>)-2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">AL2</th><td style="text-align:left">=PosHistory(<font color="Blue">C2:AJ2</font>)</td></tr></tbody></table></td></tr></table><br />
Code: 
Function PosHistory(myRange As Range, Optional myPos As Integer = 10) As String
    Dim myCell As Range, i As Integer, Total() As Long, myPosHistory As String
    ReDim Total(1 To myPos)
    For Each myCell In myRange
        For i = 1 To myPos
            If myCell = i Then
                Total(i) = Total(i) + 1
                Exit For
            End If
        Next i
    Next myCell
    For i = 1 To myPos
        If Total(i) > 0 Then
            myPosHistory = myPosHistory & Total(i) & " P" & i & ", "
        End If
    Next i
    PosHistory = Left(myPosHistory, Len(myPosHistory) - 2)
End Function


Markmzz
 
Last edited:
Upvote 0
Thanks Mark !! The formula works like a charm.

By the way, if anyone else gets the Excel error with the formula being too big, a nice workaround is to paste the formula in google's spreadsheet editor and export it back... the MS office will accept it :)
 
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,586
Messages
6,179,711
Members
452,939
Latest member
WCrawford

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