# formula problem



## bcselect (Dec 24, 2022)

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.



Super Bowl New Plus Plus.xlsmBCDEFGHIJKLMNORST1Broncos2#9358102764Broncos ScoresSeahawks ScoresWinner Points Winnings 76039AAAAAAAAAA00H38041BBBBBBBBBB70H38053CCCCCCCCCC   65DDDDDDDDDD   77EEEEEEEEEE   86FFFFFFFFFF   92GGGGGGGGGG   100HHHHHHHHHH   114IIIIIIIIII   128JJJJJJJJJJ   13PAYOUT = 380 points/ Score Change(Max  20 changes)   14   15380   16   17   18   19   20   21   22   2324Total winnings25Remaining$100 Score ChangeCell FormulasRangeFormulaB1B1='Input Scores'!B3N2N2=B1 & " Scores"O2O2=A2 & " Scores"T2T2=SUM(S3:S22)N3N3=IF('Input Scores'!F4="","",'Input Scores'!F$4)O3O3=IF('Input Scores'!F$5="","",'Input Scores'!F$5)R3:R22R3=IF(AND(N3="",O3=""),"",INDEX($B$2:$L$12,MATCH(P3,$B$2:$B$12,0),MATCH($Q3,$B$2:$L$2,0)))N4N4=IF('Input Scores'!G4="","",'Input Scores'!G$4)O4O4=IF('Input Scores'!G$5="","",'Input Scores'!G$5)S4S4=IF(AND(ISBLANK(R5),R4<>""),7600-T2,380)N5N5=IF('Input Scores'!H4="","",'Input Scores'!H$4)O5O5=IF('Input Scores'!H$5="","",'Input Scores'!H$5)N6N6=IF('Input Scores'!I4="","",'Input Scores'!I$4)O6O6=IF('Input Scores'!I$5="","",'Input Scores'!I$5)N7N7=IF('Input Scores'!J4="","",'Input Scores'!J$4)O7O7=IF('Input Scores'!J$5="","",'Input Scores'!J$5)N8N8=IF('Input Scores'!K$4="","",'Input Scores'!K$4)O8O8=IF('Input Scores'!K$5="","",'Input Scores'!K$5)N9N9=IF('Input Scores'!L$4="","",'Input Scores'!L$4)O9O9=IF('Input Scores'!L$5="","",'Input Scores'!L$5)N10N10=IF('Input Scores'!M$4="","",'Input Scores'!M$4)O10O10=IF('Input Scores'!M$5="","",'Input Scores'!M$5)N11N11=IF('Input Scores'!N$4="","",'Input Scores'!N$4)O11O11=IF('Input Scores'!N$5="","",'Input Scores'!N$5)N12N12=IF('Input Scores'!O$4="","",'Input Scores'!O$4)O12O12=IF('Input Scores'!O$5="","",'Input Scores'!O$5)N13N13=IF('Input Scores'!P$4="","",'Input Scores'!P$4)O13O13=IF('Input Scores'!P$5="","",'Input Scores'!P$5)N14N14=IF('Input Scores'!Q$4="","",'Input Scores'!Q$4)O14O14=IF('Input Scores'!Q$5="","",'Input Scores'!Q$5)N15N15=IF('Input Scores'!R$4="","",'Input Scores'!R$4)O15O15=IF('Input Scores'!R$5="","",'Input Scores'!R$5)N16N16=IF('Input Scores'!S$4="","",'Input Scores'!S$4)O16O16=IF('Input Scores'!S$5="","",'Input Scores'!S$5)N17N17=IF('Input Scores'!T$4="","",'Input Scores'!T$4)O17O17=IF('Input Scores'!T$5="","",'Input Scores'!T$5)N18N18=IF('Input Scores'!U$4="","",'Input Scores'!U$4)O18O18=IF('Input Scores'!U$5="","",'Input Scores'!U$5)N19N19=IF('Input Scores'!V$4="","",'Input Scores'!V$4)O19O19=IF('Input Scores'!V$5="","",'Input Scores'!V$5)N20N20=IF('Input Scores'!W$4="","",'Input Scores'!W$4)O20O20=IF('Input Scores'!W$5="","",'Input Scores'!W$5)N21N21=IF('Input Scores'!X$4="","",'Input Scores'!X$4)O21O21=IF('Input Scores'!X$5="","",'Input Scores'!X$5)N22N22=IF('Input Scores'!Y$4="","",'Input Scores'!Y$4)O22O22=IF('Input Scores'!Y$5="","",'Input Scores'!Y$5)


----------



## Eric W (Dec 24, 2022)

I have some questions about your set up, but maybe something like this:

Book1ABCDEFGHIJKLMNORST1Broncos2Seahawks#9358102764Broncos ScoresSeahawks ScoresWinner Points Winnings 760039AAAAAAAAAA00H38041BBBBBBBBBB70E38053CCCCCCCCCC73E38065DDDDDDDDDD710E38077EEEEEEEEEE1410I38086FFFFFFFFFF2110B570092GGGGGGGGGG100HHHHHHHHHH114IIIIIIIIII128JJJJJJJJJJ13PAYOUT = 380 points/ Score Change(Max  20 changes)1415380161718192021222324Total winnings25RemainingSheet1Cell FormulasRangeFormulaN2N2=B1 & " Scores"O2O2=A2 & " Scores"T2T2=SUM(S3:S22)R3:R22R3=IF(N3:N22<>"",INDEX(C3:L12,MATCH(RIGHT(N3:N22)+0,B3:B12,0),MATCH(RIGHT(O3:O22)+0,C2:L2,0)),"")S3:S22S3=IF(R3:R22="","",IF(R4:R23="",7600-(COUNT(N3:N22)-1)*380,380))Dynamic array formulas.


----------



## bcselect (Dec 25, 2022)

Eric W said:


> I have some questions about your set up, but maybe something like this:
> 
> Book1ABCDEFGHIJKLMNORST1Broncos2Seahawks#9358102764Broncos ScoresSeahawks ScoresWinner Points Winnings 760039AAAAAAAAAA00H38041BBBBBBBBBB70E38053CCCCCCCCCC73E38065DDDDDDDDDD710E38077EEEEEEEEEE1410I38086FFFFFFFFFF2110B570092GGGGGGGGGG100HHHHHHHHHH114IIIIIIIIII128JJJJJJJJJJ13PAYOUT = 380 points/ Score Change(Max  20 changes)1415380161718192021222324Total winnings25RemainingSheet1Cell FormulasRangeFormulaN2N2=B1 & " Scores"O2O2=A2 & " Scores"T2T2=SUM(S3:S22)R3:R22R3=IF(N3:N22<>"",INDEX(C3:L12,MATCH(RIGHT(N3:N22)+0,B3:B12,0),MATCH(RIGHT(O3:O22)+0,C2:L2,0)),"")S3:S22S3=IF(R3:R22="","",IF(R4:R23="",7600-(COUNT(N3:N22)-1)*380,380))Dynamic array formulas.


Looks like it would work but I get a #Spill error.


----------



## bcselect (Dec 25, 2022)

I was wrong.  Posted formula incorrectly.  Almost working perfectly.  Only thing not working is the last winner should get the remaining balance, not 380


----------



## bcselect (Dec 26, 2022)

bcselect said:


> Looks like it would work but I get a #Spill error.


With your help I got it.  Thanks.


----------



## Eric W (Dec 26, 2022)

Glad you got it sorted.


----------



## smozgur (Dec 26, 2022)

bcselect said:


> With your help I got it.  Thanks.


The marked solution post has been switched accordingly.


----------

