Standard football board. Columns N thru S are where the results are posted. The winner is awarded 380 points with each score change. 7600 points are available totally. Here's the problem.
Depending on the number of score changes, the number of points available varies. The last score change awards the balance remaining, not just 380. I can't come up with a formula to do this that doesn't give me an error.
Depending on the number of score changes, the number of points available varies. The last score change awards the balance remaining, not just 380. I can't come up with a formula to do this that doesn't give me an error.
| Super Bowl New Plus Plus.xlsm | |||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | M | N | O | R | S | T | |||||
| 1 | Broncos | ||||||||||||||||||||
| 2 | # | 9 | 3 | 5 | 8 | 1 | 0 | 2 | 7 | 6 | 4 | Broncos Scores | Seahawks Scores | Winner | Points Winnings | 760 | |||||
| 3 | 9 | A | A | A | A | A | A | A | A | A | A | 0 | 0 | H | 380 | ||||||
| 4 | 1 | B | B | B | B | B | B | B | B | B | B | 7 | 0 | H | 380 | ||||||
| 5 | 3 | C | C | C | C | C | C | C | C | C | C | ||||||||||
| 6 | 5 | D | D | D | D | D | D | D | D | D | D | ||||||||||
| 7 | 7 | E | E | E | E | E | E | E | E | E | E | ||||||||||
| 8 | 6 | F | F | F | F | F | F | F | F | F | F | ||||||||||
| 9 | 2 | G | G | G | G | G | G | G | G | G | G | ||||||||||
| 10 | 0 | H | H | H | H | H | H | H | H | H | H | ||||||||||
| 11 | 4 | I | I | I | I | I | I | I | I | I | I | ||||||||||
| 12 | 8 | J | J | J | J | J | J | J | J | J | J | ||||||||||
| 13 | PAYOUT = | 380 points/ Score Change | (Max 20 changes) | ||||||||||||||||||
| 14 | |||||||||||||||||||||
| 15 | 380 | ||||||||||||||||||||
| 16 | |||||||||||||||||||||
| 17 | |||||||||||||||||||||
| 18 | |||||||||||||||||||||
| 19 | |||||||||||||||||||||
| 20 | |||||||||||||||||||||
| 21 | |||||||||||||||||||||
| 22 | |||||||||||||||||||||
| 23 | |||||||||||||||||||||
| 24 | Total winnings | ||||||||||||||||||||
| 25 | Remaining | ||||||||||||||||||||
$100 Score Change | |||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B1 | B1 | ='Input Scores'!B3 |
| N2 | N2 | =B1 & " Scores" |
| O2 | O2 | =A2 & " Scores" |
| T2 | T2 | =SUM(S3:S22) |
| N3 | N3 | =IF('Input Scores'!F4="","",'Input Scores'!F$4) |
| O3 | O3 | =IF('Input Scores'!F$5="","",'Input Scores'!F$5) |
| R3:R22 | R3 | =IF(AND(N3="",O3=""),"",INDEX($B$2:$L$12,MATCH(P3,$B$2:$B$12,0),MATCH($Q3,$B$2:$L$2,0))) |
| N4 | N4 | =IF('Input Scores'!G4="","",'Input Scores'!G$4) |
| O4 | O4 | =IF('Input Scores'!G$5="","",'Input Scores'!G$5) |
| S4 | S4 | =IF(AND(ISBLANK(R5),R4<>""),7600-T2,380) |
| N5 | N5 | =IF('Input Scores'!H4="","",'Input Scores'!H$4) |
| O5 | O5 | =IF('Input Scores'!H$5="","",'Input Scores'!H$5) |
| N6 | N6 | =IF('Input Scores'!I4="","",'Input Scores'!I$4) |
| O6 | O6 | =IF('Input Scores'!I$5="","",'Input Scores'!I$5) |
| N7 | N7 | =IF('Input Scores'!J4="","",'Input Scores'!J$4) |
| O7 | O7 | =IF('Input Scores'!J$5="","",'Input Scores'!J$5) |
| N8 | N8 | =IF('Input Scores'!K$4="","",'Input Scores'!K$4) |
| O8 | O8 | =IF('Input Scores'!K$5="","",'Input Scores'!K$5) |
| N9 | N9 | =IF('Input Scores'!L$4="","",'Input Scores'!L$4) |
| O9 | O9 | =IF('Input Scores'!L$5="","",'Input Scores'!L$5) |
| N10 | N10 | =IF('Input Scores'!M$4="","",'Input Scores'!M$4) |
| O10 | O10 | =IF('Input Scores'!M$5="","",'Input Scores'!M$5) |
| N11 | N11 | =IF('Input Scores'!N$4="","",'Input Scores'!N$4) |
| O11 | O11 | =IF('Input Scores'!N$5="","",'Input Scores'!N$5) |
| N12 | N12 | =IF('Input Scores'!O$4="","",'Input Scores'!O$4) |
| O12 | O12 | =IF('Input Scores'!O$5="","",'Input Scores'!O$5) |
| N13 | N13 | =IF('Input Scores'!P$4="","",'Input Scores'!P$4) |
| O13 | O13 | =IF('Input Scores'!P$5="","",'Input Scores'!P$5) |
| N14 | N14 | =IF('Input Scores'!Q$4="","",'Input Scores'!Q$4) |
| O14 | O14 | =IF('Input Scores'!Q$5="","",'Input Scores'!Q$5) |
| N15 | N15 | =IF('Input Scores'!R$4="","",'Input Scores'!R$4) |
| O15 | O15 | =IF('Input Scores'!R$5="","",'Input Scores'!R$5) |
| N16 | N16 | =IF('Input Scores'!S$4="","",'Input Scores'!S$4) |
| O16 | O16 | =IF('Input Scores'!S$5="","",'Input Scores'!S$5) |
| N17 | N17 | =IF('Input Scores'!T$4="","",'Input Scores'!T$4) |
| O17 | O17 | =IF('Input Scores'!T$5="","",'Input Scores'!T$5) |
| N18 | N18 | =IF('Input Scores'!U$4="","",'Input Scores'!U$4) |
| O18 | O18 | =IF('Input Scores'!U$5="","",'Input Scores'!U$5) |
| N19 | N19 | =IF('Input Scores'!V$4="","",'Input Scores'!V$4) |
| O19 | O19 | =IF('Input Scores'!V$5="","",'Input Scores'!V$5) |
| N20 | N20 | =IF('Input Scores'!W$4="","",'Input Scores'!W$4) |
| O20 | O20 | =IF('Input Scores'!W$5="","",'Input Scores'!W$5) |
| N21 | N21 | =IF('Input Scores'!X$4="","",'Input Scores'!X$4) |
| O21 | O21 | =IF('Input Scores'!X$5="","",'Input Scores'!X$5) |
| N22 | N22 | =IF('Input Scores'!Y$4="","",'Input Scores'!Y$4) |
| O22 | O22 | =IF('Input Scores'!Y$5="","",'Input Scores'!Y$5) |
