Formula to display previous result?

bluegold

Active Member
Joined
Jun 21, 2009
Messages
277
I need a formula to calculate a team's last game result - i.e Column L & M
Where column L is the Favourite team's last result and column M is the non-favourite team's last result. I have manually entered the answers to show the format I want results shown as. The previous game's scores are located in column's Q & R (For & Against). I'm guessing helper columns will be required. Any idea's as I'm stumped!

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 14px;"> <col style="width: 73px;"> <col style="width: 73px;"> <col style="width: 48px;"> <col style="width: 38px;"> <col style="width: 45px;"> <col style="width: 31px;"> <col style="width: 44px;"> <col style="width: 46px;"> <col style="width: 39px;"> <col style="width: 39px;"> <col style="width: 32px;"> <col style="width: 32px;"> <col style="width: 45px;"> <col style="width: 38px;"> <col style="width: 33px;"> <col style="width: 28px;"> <col style="width: 28px;"> <col style="width: 45px;"> <col style="width: 45px;"> <col style="width: 67px;"> <col style="width: 86px;"> <col style="width: 94px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td> <td>I</td> <td>J</td> <td>K</td> <td>L</td> <td>M</td> <td>N</td> <td>O</td> <td>P</td> <td>Q</td> <td>R</td> <td>S</td> <td>T</td> <td>U</td> <td>V</td> <td>W</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">=</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">FAV</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">N-FAV</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">DATE</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">WIN</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">LOSS</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">RND</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">TABLE</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">%CH</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">W/L</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">W/L 2</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">Last</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">Last</td> <td style="text-align: left; color: rgb(128, 0, 0); font-size: 9pt; font-weight: bold;">L$</td> <td style="text-align: left; color: rgb(0, 128, 0); font-size: 9pt; font-weight: bold;">W$</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">RES</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">F</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">A</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">W2</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">L2</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">N-FAV RES</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">CURRENT W/L</td> <td style="text-align: left; font-size: 9pt; font-weight: bold;">CURRENT W/L2</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td style="text-align: center; font-size: 9pt;">h</td> <td style="text-align: left; font-size: 9pt;">gcoast</td> <td style="text-align: left; font-size: 9pt;">nqld</td> <td style="text-align: left; font-size: 9pt;">14/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.62</td> <td style="text-align: left; font-size: 9pt;">$2.30</td> <td style="text-align: left; font-size: 9pt;">1</td> <td style="text-align: left; font-size: 9pt;">down</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="text-align: left; color: rgb(0, 128, 0); font-size: 9pt;">$1.62</td> <td style="text-align: left;">won</td> <td style="text-align: left; font-size: 9pt;">36</td> <td style="text-align: left; font-size: 9pt;">18</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">loss</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w01</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l01</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td style="text-align: center; font-size: 9pt;">a</td> <td style="text-align: left; font-size: 9pt;">sydney</td> <td style="text-align: left; font-size: 9pt;">souths</td> <td style="text-align: left; font-size: 9pt;">14/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.85</td> <td style="text-align: left; font-size: 9pt;">$1.95</td> <td style="text-align: left; font-size: 9pt;">1</td> <td style="text-align: left; font-size: 9pt;">down</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="text-align: left; color: rgb(0, 128, 0); font-size: 9pt;">$1.85</td> <td style="text-align: left;">won</td> <td style="text-align: left; font-size: 9pt;">34</td> <td style="text-align: left; font-size: 9pt;">20</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">loss</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w01</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l01</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td style="text-align: center; font-size: 9pt;">h</td> <td style="text-align: left; font-size: 9pt;">manly</td> <td style="text-align: left; font-size: 9pt;">cronulla</td> <td style="text-align: left; font-size: 9pt;">15/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.45</td> <td style="text-align: left; font-size: 9pt;">$2.75</td> <td style="text-align: left; font-size: 9pt;">1</td> <td style="text-align: left; font-size: 9pt;">up</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; color: rgb(128, 0, 0); font-size: 9pt;">$2.75</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">
</td> <td style="text-align: left;">loss</td> <td style="text-align: left; font-size: 9pt;">10</td> <td style="text-align: left; font-size: 9pt;">16</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">won</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l01</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w01</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td style="text-align: center; font-size: 9pt;">h</td> <td style="text-align: left; font-size: 9pt;">newcastle</td> <td style="text-align: left; font-size: 9pt;">canberra</td> <td style="text-align: left; font-size: 9pt;">15/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.55</td> <td style="text-align: left; font-size: 9pt;">$2.45</td> <td style="text-align: left; font-size: 9pt;">1</td> <td style="text-align: left; font-size: 9pt;">down</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="text-align: left; color: rgb(0, 128, 0); font-size: 9pt;">$1.55</td> <td style="text-align: left;">won</td> <td style="text-align: left; font-size: 9pt;">30</td> <td style="text-align: left; font-size: 9pt;">14</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">loss</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w01</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l01</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td style="text-align: center; font-size: 9pt;">h</td> <td style="text-align: left; font-size: 9pt;">parramatta</td> <td style="text-align: left; font-size: 9pt;">canterbury</td> <td style="text-align: left; font-size: 9pt;">15/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.48</td> <td style="text-align: left; font-size: 9pt;">$2.65</td> <td style="text-align: left; font-size: 9pt;">1</td> <td style="text-align: left; font-size: 9pt;">up</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="text-align: left; color: rgb(0, 128, 0); font-size: 9pt;">$1.48</td> <td style="text-align: left;">won</td> <td style="text-align: left; font-size: 9pt;">28</td> <td style="text-align: left; font-size: 9pt;">20</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">loss</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w01</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l01</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td style="text-align: center; font-size: 9pt;">h</td> <td style="text-align: left; font-size: 9pt;">brisbane</td> <td style="text-align: left; font-size: 9pt;">penrith</td> <td style="text-align: left; font-size: 9pt;">16/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.43</td> <td style="text-align: left; font-size: 9pt;">$2.85</td> <td style="text-align: left; font-size: 9pt;">1</td> <td style="text-align: left; font-size: 9pt;">up</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="text-align: left; color: rgb(0, 128, 0); font-size: 9pt;">$1.43</td> <td style="text-align: left;">won</td> <td style="text-align: left; font-size: 9pt;">48</td> <td style="text-align: left; font-size: 9pt;">12</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">loss</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w01</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l01</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td style="text-align: center; font-size: 9pt;">a</td> <td style="text-align: left; font-size: 9pt;">sgeorge</td> <td style="text-align: left; font-size: 9pt;">wests</td> <td style="text-align: left; font-size: 9pt;">16/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.75</td> <td style="text-align: left; font-size: 9pt;">$2.08</td> <td style="text-align: left; font-size: 9pt;">1</td> <td style="text-align: left; font-size: 9pt;">down</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; color: rgb(128, 0, 0); font-size: 9pt;">$1.00</td> <td style="text-align: left; color: rgb(0, 128, 0); font-size: 9pt;">$1.00</td> <td style="text-align: left;">draw</td> <td style="text-align: left; font-size: 9pt;">16</td> <td style="text-align: left; font-size: 9pt;">16</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">won</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l01</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w01</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">9</td> <td style="text-align: center; font-size: 9pt;">h</td> <td style="text-align: left; font-size: 9pt;">melbourne</td> <td style="text-align: left; font-size: 9pt;">nzealand</td> <td style="text-align: left; font-size: 9pt;">17/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.28</td> <td style="text-align: left; font-size: 9pt;">$3.70</td> <td style="text-align: left; font-size: 9pt;">1</td> <td style="text-align: left; font-size: 9pt;">up</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="text-align: left; font-size: 9pt;">d00</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="text-align: left; color: rgb(0, 128, 0); font-size: 9pt;">$1.28</td> <td style="text-align: left;">won</td> <td style="text-align: left; font-size: 9pt;">32</td> <td style="text-align: left; font-size: 9pt;">18</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">loss</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w01</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l01</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">10</td> <td style="text-align: center; font-size: 9pt;">a</td> <td style="text-align: left; font-size: 9pt;">canterbury</td> <td style="text-align: left; font-size: 9pt;">souths</td> <td style="text-align: left; font-size: 9pt;">21/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.82</td> <td style="text-align: left; font-size: 9pt;">$2.00</td> <td style="text-align: left; font-size: 9pt;">2</td> <td style="text-align: left; font-size: 9pt;">up</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">l01</td> <td style="text-align: left; font-size: 9pt;">l01</td> <td style="text-align: left; font-size: 9pt;">l08</td> <td style="text-align: left; font-size: 9pt;">l14</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="text-align: left; color: rgb(0, 128, 0); font-size: 9pt;">$1.82</td> <td style="text-align: left;">won</td> <td style="text-align: left; font-size: 9pt;">25</td> <td style="text-align: left; font-size: 9pt;">12</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">loss</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w01</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l02</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">11</td> <td style="text-align: center; font-size: 9pt;">h</td> <td style="text-align: left; font-size: 9pt;">sydney</td> <td style="text-align: left; font-size: 9pt;">brisbane</td> <td style="text-align: left; font-size: 9pt;">21/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.68</td> <td style="text-align: left; font-size: 9pt;">$2.20</td> <td style="text-align: left; font-size: 9pt;">2</td> <td style="text-align: left; font-size: 9pt;">down</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">w01</td> <td style="text-align: left; font-size: 9pt;">w01</td> <td style="text-align: left; font-size: 9pt;">w14</td> <td style="text-align: left; font-size: 9pt;">w36</td> <td style="text-align: left; color: rgb(128, 0, 0); font-size: 9pt;">$2.20</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">
</td> <td style="text-align: left;">loss</td> <td style="text-align: left; font-size: 9pt;">14</td> <td style="text-align: left; font-size: 9pt;">20</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">won</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l01</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w02</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">12</td> <td style="text-align: center; font-size: 9pt;">a</td> <td style="text-align: left; font-size: 9pt;">manly</td> <td style="text-align: left; font-size: 9pt;">newcastle</td> <td style="text-align: left; font-size: 9pt;">22/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.60</td> <td style="text-align: left; font-size: 9pt;">$2.35</td> <td style="text-align: left; font-size: 9pt;">2</td> <td style="text-align: left; font-size: 9pt;">down</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">l01</td> <td style="text-align: left; font-size: 9pt;">w01</td> <td style="text-align: left; font-size: 9pt;">l06</td> <td style="text-align: left; font-size: 9pt;">w16</td> <td style="text-align: left; color: rgb(128, 0, 0); font-size: 9pt;">$2.35</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">
</td> <td style="text-align: left;">loss</td> <td style="text-align: left; font-size: 9pt;">12</td> <td style="text-align: left; font-size: 9pt;">13</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">won</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l02</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w02</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">13</td> <td style="text-align: center; font-size: 9pt;">h</td> <td style="text-align: left; font-size: 9pt;">nqld</td> <td style="text-align: left; font-size: 9pt;">wests</td> <td style="text-align: left; font-size: 9pt;">22/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.33</td> <td style="text-align: left; font-size: 9pt;">$3.35</td> <td style="text-align: left; font-size: 9pt;">2</td> <td style="text-align: left; font-size: 9pt;">down</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">l01</td> <td style="text-align: left; font-size: 9pt;">w01</td> <td style="text-align: left; font-size: 9pt;">l18</td> <td style="text-align: left; font-size: 9pt;">d16</td> <td style="text-align: left; color: rgb(128, 0, 0); font-size: 9pt;">$3.35</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">
</td> <td style="text-align: left;">loss</td> <td style="text-align: left; font-size: 9pt;">10</td> <td style="text-align: left; font-size: 9pt;">30</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">won</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l02</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w02</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">14</td> <td style="text-align: center; font-size: 9pt;">h</td> <td style="text-align: left; font-size: 9pt;">penrith</td> <td style="text-align: left; font-size: 9pt;">canberra</td> <td style="text-align: left; font-size: 9pt;">22/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.58</td> <td style="text-align: left; font-size: 9pt;">$2.40</td> <td style="text-align: left; font-size: 9pt;">2</td> <td style="text-align: left; font-size: 9pt;">down</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">l01</td> <td style="text-align: left; font-size: 9pt;">l01</td> <td style="text-align: left; font-size: 9pt;">l36</td> <td style="text-align: left; font-size: 9pt;">l16</td> <td style="text-align: left; color: rgb(128, 0, 0); font-size: 9pt;">$2.40</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">
</td> <td style="text-align: left;">loss</td> <td style="text-align: left; font-size: 9pt;">16</td> <td style="text-align: left; font-size: 9pt;">20</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">won</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l02</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w01</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">15</td> <td style="text-align: center; font-size: 9pt;">h</td> <td style="text-align: left; font-size: 9pt;">melbourne</td> <td style="text-align: left; font-size: 9pt;">cronulla</td> <td style="text-align: left; font-size: 9pt;">23/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.36</td> <td style="text-align: left; font-size: 9pt;">$3.20</td> <td style="text-align: left; font-size: 9pt;">2</td> <td style="text-align: left; font-size: 9pt;">up</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">w01</td> <td style="text-align: left; font-size: 9pt;">w01</td> <td style="text-align: left; font-size: 9pt;">w14</td> <td style="text-align: left; font-size: 9pt;">w06</td> <td style="text-align: left; color: rgb(128, 0, 0); font-size: 9pt;">$3.20</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">
</td> <td style="text-align: left;">loss</td> <td style="text-align: left; font-size: 9pt;">16</td> <td style="text-align: left; font-size: 9pt;">17</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">won</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l01</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w02</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">16</td> <td style="text-align: center; font-size: 9pt;">a</td> <td style="text-align: left; font-size: 9pt;">parramatta</td> <td style="text-align: left; font-size: 9pt;">nzealand</td> <td style="text-align: left; font-size: 9pt;">23/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.82</td> <td style="text-align: left; font-size: 9pt;">$2.00</td> <td style="text-align: left; font-size: 9pt;">2</td> <td style="text-align: left; font-size: 9pt;">up</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">w01</td> <td style="text-align: left; font-size: 9pt;">l01</td> <td style="text-align: left; font-size: 9pt;">w08</td> <td style="text-align: left; font-size: 9pt;">l14</td> <td style="text-align: left; color: rgb(128, 0, 0); font-size: 9pt;">$2.00</td> <td style="color: rgb(0, 128, 0); font-size: 9pt;">
</td> <td style="text-align: left;">loss</td> <td style="text-align: left; font-size: 9pt;">16</td> <td style="text-align: left; font-size: 9pt;">30</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">won</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l01</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w01</td></tr> <tr style="height: 15px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">17</td> <td style="text-align: center; font-size: 9pt;">h</td> <td style="text-align: left; font-size: 9pt;">sgeorge</td> <td style="text-align: left; font-size: 9pt;">gcoast</td> <td style="text-align: left; font-size: 9pt;">24/3/08</td> <td style="text-align: left; font-size: 9pt;">$1.65</td> <td style="text-align: left; font-size: 9pt;">$2.25</td> <td style="text-align: left; font-size: 9pt;">2</td> <td style="text-align: left; font-size: 9pt;">down</td> <td style="font-size: 9pt;">
</td> <td style="text-align: left; font-size: 9pt;">l01</td> <td style="text-align: left; font-size: 9pt;">w01</td> <td style="text-align: left; font-size: 9pt;">d16</td> <td style="text-align: left; font-size: 9pt;">w18</td> <td style="color: rgb(128, 0, 0); font-size: 9pt;">
</td> <td style="text-align: left; color: rgb(0, 128, 0); font-size: 9pt;">$1.65</td> <td style="text-align: left;">won</td> <td style="text-align: left; font-size: 9pt;">30</td> <td style="text-align: left; font-size: 9pt;">12</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="font-size: 9pt; font-weight: bold;">
</td> <td style="text-align: left; font-size: 9pt;">loss</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">w01</td> <td style="text-align: left; font-family: Arial Unicode MS; font-size: 9pt;">l01</td></tr></tbody></table>
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

bluegold

Active Member
Joined
Jun 21, 2009
Messages
277
"d00" is displayed because there isn't any previous results to display. Basically the first round of matches (first 8 rows) will show d00 to start with. But all matches after the first round of matches will have previous matches to refer to. Also note a draw is displayed as the points scored preceeded by a "d" instead of "w" or "l" as shown in column M13.
 

energman58

Well-known Member
Joined
Oct 25, 2010
Messages
553
It would be a lot easier if you did it the other way up - latest at the top as you will probably need to use MATCH or a lookup to find your team's last game and MATCH works down the column only

So if your column looks like this

1 Jan Manly
12 Dec St George
11 Dec Penrith
5 Dec Manly

You can find the last game simply by looking down using MATCH in both your FAV and Non FAV columns and picking the smaller of the two values but if it is the other way up you wont be able to do it and you will have to search using VBA if you search upwards

Would that be a problem?
 

bluegold

Active Member
Joined
Jun 21, 2009
Messages
277

ADVERTISEMENT

I prefer it to be chronological (oldest games first). Because that way I can add games to the spreadsheet in sequence. Not sure if this is any help but I can show you the formula to calculate win / loss streak's for Favourite & non-favourite teams as shown in column's J & K.

<table style="border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); font-family: Arial; color: rgb(0, 0, 0); font-size: 10pt;"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>J2</td> <td>=LOOKUP(9.99E+307,CHOOSE({1,2},0,INDIRECT("R"&SUBSTITUTE(LOOKUP(9.99E+307,SIGN(SEARCH("@"&B2&"@","@"&$B$1:$B1&"@"&$C$1:$C1&"@"))*ROW($B$1:$B1)+0.022+(0.001*($C$1:$C1=B2))),".","C"),FALSE)))</td></tr> <tr> <td>K2</td> <td>=LOOKUP(9.99E+307,CHOOSE({1,2},0,INDIRECT("R"&SUBSTITUTE(LOOKUP(9.99E+307,SIGN(SEARCH("@"&C2&"@","@"&$B$1:$B1&"@"&$C$1:$C1&"@"))*ROW($B$1:$B1)+0.022+(0.001*($C$1:$C1=C2))),".","C"),FALSE)))</td></tr></tbody></table></td></tr></tbody></table>
Helper columns for above:

<table border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>U2</td> <td>=P2*-1</td></tr> <tr> <td>V2</td> <td>=IF(P2,SUM(P2,J2*(SIGN(P2)=SIGN(INT(J2)))),(HOUR(ABS(J2))+1)/22)</td></tr> <tr> <td>W2</td> <td>=IF(U2,SUM(U2,K2*(SIGN(U2)=SIGN(INT(K2)))),(HOUR(ABS(K2))+1)/22)</td></tr></tbody></table>
 

energman58

Well-known Member
Joined
Oct 25, 2010
Messages
553

ADVERTISEMENT

I think this is the first part :eek: of the formula for L17 - I am sure you can extend it as appropriate - where it says "needs more" here you need to replicate the first part of the formula to account for the case where the team is the non favourite - (the second part of the first if) so all the references to the B2:B16 become references to C and you need to turn the greater than and less then formulas in the fiirst part around and multiply the points differences by -1.

I said it would be MUCH easier if it was the other way up - most of teh complexity involves finding the most recent game
<?XML:NAMESPACE PREFIX = INDEX(Q1 /><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More>
<INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More>The expression is so long the code tags dont work!</INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
<INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
<INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More>So here it is in bits</INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
<INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
<INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More>=IF(SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16)))>SUMPRODUCT(MAX(($C$2:C16=B17)*ROW($C$2:C16))),IF(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)>INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),</INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
<INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
<INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More>"w"&INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)-INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),IF(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1),<INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),< INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16='B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need'></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
<INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
<INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More>"Need More Here")</INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
<INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
<INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
<INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More>There will be a slightly less complex expression using array formulas (maybe) or helper columns using the same logic - I have not tried to produce something where the team has not played before - probably you need to capture that too!</INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
<INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
<INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More><INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need p Here?)< More>Good luck
</INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
</INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
</INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
</INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
</INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),<></INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX(Q1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))*R17))-(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX(Q1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),"Need>
 
Last edited:

bluegold

Active Member
Joined
Jun 21, 2009
Messages
277
Thanks for your formula energman58. Unfortunately I'm a novice excel user at best let alone a formula guru so I wouldn't know how to modify your formula to produce the correct results like returning a d00 when no prior results avail etc. Sounds like an array with helper columns might be the go - like was done in a previous column's formula I displayed.
 

energman58

Well-known Member
Joined
Oct 25, 2010
Messages
553
If you want to persevere with the big formula my version is almost there - I just got bored with wrangling it! It is just me and teh dog at home tonight so I may have another try.

However, I would probably split it up to find the most recent match for the team and then separately determine if it is a win or a loss etc.
 

energman58

Well-known Member
Joined
Oct 25, 2010
Messages
553
here is the complete formula for L17

You need to fix it for M17 but you can do most of it by dragging

As it is it works provided that both teams have played previously - it obvioulsy falls over if they have not

=IF(SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16)))>SUMPRODUCT(MAX(($C$2:C16=B17)*ROW($C$2:C16))),IF(INDEX($Q$1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)>INDEX($Q$1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)

*ROW($B$2:B16))),2),"w"&INDEX($Q$1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)-INDEX($Q$1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),IF(INDEX($Q$1:Q17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)<INDEX($Q$1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),2),"l"&(INDEX($Q$1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)

*ROW($B$2:B16))*R17))-(INDEX($Q$1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1))),"d"&(INDEX($Q$1:R17,SUMPRODUCT(MAX(($B$2:B16=B17)*ROW($B$2:B16))),1)))),IF(INDEX($Q$1:R17,SUMPRODUCT(MAX(($C$2:C16=B17)*ROW($C$2:C16))),2)>INDEX($Q$1:R17,SUMPRODUCT(MAX(($C$2:C16=B17)

*ROW($C$2:C16))),1),"w"&INDEX($Q$1:R17,SUMPRODUCT(MAX(($C$2:C16=B17)*ROW($C$2:C16))),2)-INDEX($Q$1:R17,SUMPRODUCT(MAX(($C$2:C16=B17)*ROW($C$2:C16))),1),IF(INDEX($Q$1:R17,SUMPRODUCT(MAX(($C$2:C16=B17)*ROW($C$2:C16))),2)

<INDEX($Q$1:R17,SUMPRODUCT(MAX(($C$2:C16=B17)*ROW($C$2:C16))),1),"l"&(INDEX($Q$1:R17,SUMPRODUCT(MAX(($C$2:C16=B17)*ROW($C$2:C16))*R17),1)-(INDEX($Q$1:R17,SUMPRODUCT(MAX(($C$2:C16=B17)*ROW($C$2:C16))),2))),"d"&(INDEX($Q$1:Q17,SUMPRODUCT(MAX(($C$2:C16=B17)*ROW($C$2:C16))),1)))))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,841
Messages
5,598,388
Members
414,234
Latest member
grlevesq

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
Top