# W/L record, Home or Away



## gtd526 (Dec 30, 2022)

Hello,
I'm trying to record the W/L record of a Team whether its Home or Away.
Right now the "formula" doesn't care if its Home or Away, using a SUMPRODUCT formula.
Thank you.

Book1.xlsmABCDEFGHIJKLM2Only "Home"  -  Odd rows only(A:A)Only "AWAY"  -  Even rows only(A:A)3TeamATSTeamWLPshTeamWLPsh4WAS-5LATL100ATL1005ORL5WBOS000BOS0006LAL6.5LBKN000BKN0007ATL-6.5WCHA000CHA0008PHX2WCHI010CHI0109TOR-2LCLE000CLE00010DET7WDAL000DAL00011CHI-7LDEN010DEN01012MIN6PSHDET100DET10013MIL-6PSHGSW010GSW01014PHI1LHOU000HOU00015NOP-1WIND000IND00016MIA3.5WLAC000LAC00017DEN-3.5LLAL010LAL01018POR-2.5WMEM000MEM00019GSW2.5LMIA100MIA10020UTA3WMIL0.50.51MIL0.50.5121SAC-3LMIN0.50.51MIN0.50.5122NOP100NOP10023  NYK000NYK00024OKC000OKC00025  ORL100ORL10026PHI010PHI01027  PHX100PHX10028POR100POR10029  SAC010SAC01030SAN000SAN00031  TOR010TOR01032UTA100UTA10033  WAS010WAS010Sheet1Cell FormulasRangeFormulaF4:F33F4=SUMPRODUCT(($A$4:$A$33=$E4)*($C$4:$C$33="W"))+($H4*0.5)G4:G33G4=SUMPRODUCT(($A$4:$A$33=$E4)*($C$4:$C$33="L"))+($H4*0.5)M4:M33,H4:H33H4=SUMPRODUCT(($A$4:$A$33=$E4)*($C$4:$C$33="psh"))K4:K33K4=SUMPRODUCT(($A$4:$A$33=$J4)*($C$4:$C$33="W"))+($M4*0.5)L4:L33L4=SUMPRODUCT(($A$4:$A$33=$J4)*($C$4:$C$33="L"))+($M4*0.5)B5,B33,B31,B29,B27,B25,B23,B21,B19,B17,B15,B13,B11,B9,B7B5=IF(ISTEXT(A4),IF(B4="PK","PK",IF(B4<0,B4*-1,B4*-1)),"")C5,C33,C31,C29,C27,C25,C23C5=IF(C4="W","L",IF(C4="L","W",IF(C4="PSH","PSH","")))Press CTRL+SHIFT+ENTER to enter array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If TrueA4:A33Expression=AND($AL4>=$AL$1,$AL4<>"")textNOB4:C11Cellcontains a blank value textNOC4:C33Cell Value="psh"textNOC4:C33Cell Value="W"textNOC4:C33Cell Value="L"textNOA4:A33Expression=NOT(ISERROR(MATCH($A4,$G$52:$G$66,0)))textNOB4:B33Cell Value<0textNOB4:B33Expression=B4<=-10textNOB4:B33Expression=AND($A4=$AE4,$B4>0,$A4<>"",$AL4<=$AL$1)textNO


----------



## kevin9999 (Dec 30, 2022)

Does this do what you want?
Book1ABCDEFGHIJKLM2Only "Home"  -  Odd rows only(A:A)Only "AWAY"  -  Even rows only(A:A)3TeamATSTeamWLPshTeamWLPsh4WAS-5LATL100ATL0005ORL5WBOS000BOS0006LAL6.5LBKN000BKN0007ATL-6.5WCHA000CHA0008PHX2WCHI010CHI0009TOR-2LCLE000CLE00010DET7WDAL000DAL00011CHI-7LDEN010DEN00012MIN6PSHDET000DET10013MIL-6PSHGSW010GSW00014PHI1LHOU000HOU00015NOP-1WIND000IND00016MIA3.5WLAC000LAC00017DEN-3.5LLAL000LAL01018POR-2.5WMEM000MEM00019GSW2.5LMIA000MIA10020UTA3WMIL001MIL00021SAC-3LMIN000MIN00122NOP100NOP00023  NYK000NYK00024OKC000OKC00025  ORL100ORL00026PHI000PHI01027  PHX000PHX10028POR000POR10029  SAC010SAC00030SAN000SAN00031  TOR010TOR00032UTA000UTA10033  WAS000WAS01034Sheet2Cell FormulasRangeFormulaF4:F33F4=SUMPRODUCT(--(MOD(ROW($A$4:$A$33),2)=1),--($A$4:$A$33=$E4),--($C$4:$C$33="W"))G4:G33G4=SUMPRODUCT(--(MOD(ROW($A$4:$A$33),2)=1),--($A$4:$A$33=$E4),--($C$4:$C$33="L"))H4:H33H4=SUMPRODUCT(--(MOD(ROW($A$4:$A$33),2)=1),--($A$4:$A$33=$E4),--($C$4:$C$33="PSH"))K4:K33K4=SUMPRODUCT(--(MOD(ROW($A$4:$A$33),2)=0),--($A$4:$A$33=$J4),--($C$4:$C$33="W"))L4:L33L4=SUMPRODUCT(--(MOD(ROW($A$4:$A$33),2)=0),--($A$4:$A$33=$J4),--($C$4:$C$33="L"))M4:M33M4=SUMPRODUCT(--(MOD(ROW($A$4:$A$33),2)=0),--($A$4:$A$33=$J4),--($C$4:$C$33="PSH"))B5,B33,B31,B29,B27,B25,B23,B21,B19,B17,B15,B13,B11,B9,B7B5=IF(ISTEXT(A4),IF(B4="PK","PK",IF(B4<0,B4*-1,B4*-1)),"")C5,C33,C31,C29,C27,C25,C23C5=IF(C4="W","L",IF(C4="L","W",IF(C4="PSH","PSH","")))


----------



## gtd526 (Dec 30, 2022)

kevin9999 said:


> Does this do what you want?
> Book1ABCDEFGHIJKLM2Only "Home"  -  Odd rows only(A:A)Only "AWAY"  -  Even rows only(A:A)3TeamATSTeamWLPshTeamWLPsh4WAS-5LATL100ATL0005ORL5WBOS000BOS0006LAL6.5LBKN000BKN0007ATL-6.5WCHA000CHA0008PHX2WCHI010CHI0009TOR-2LCLE000CLE00010DET7WDAL000DAL00011CHI-7LDEN010DEN00012MIN6PSHDET000DET10013MIL-6PSHGSW010GSW00014PHI1LHOU000HOU00015NOP-1WIND000IND00016MIA3.5WLAC000LAC00017DEN-3.5LLAL000LAL01018POR-2.5WMEM000MEM00019GSW2.5LMIA000MIA10020UTA3WMIL001MIL00021SAC-3LMIN000MIN00122NOP100NOP00023  NYK000NYK00024OKC000OKC00025  ORL100ORL00026PHI000PHI01027  PHX000PHX10028POR000POR10029  SAC010SAC00030SAN000SAN00031  TOR010TOR00032UTA000UTA10033  WAS000WAS01034Sheet2Cell FormulasRangeFormulaF4:F33F4=SUMPRODUCT(--(MOD(ROW($A$4:$A$33),2)=1),--($A$4:$A$33=$E4),--($C$4:$C$33="W"))G4:G33G4=SUMPRODUCT(--(MOD(ROW($A$4:$A$33),2)=1),--($A$4:$A$33=$E4),--($C$4:$C$33="L"))H4:H33H4=SUMPRODUCT(--(MOD(ROW($A$4:$A$33),2)=1),--($A$4:$A$33=$E4),--($C$4:$C$33="PSH"))K4:K33K4=SUMPRODUCT(--(MOD(ROW($A$4:$A$33),2)=0),--($A$4:$A$33=$J4),--($C$4:$C$33="W"))L4:L33L4=SUMPRODUCT(--(MOD(ROW($A$4:$A$33),2)=0),--($A$4:$A$33=$J4),--($C$4:$C$33="L"))M4:M33M4=SUMPRODUCT(--(MOD(ROW($A$4:$A$33),2)=0),--($A$4:$A$33=$J4),--($C$4:$C$33="PSH"))B5,B33,B31,B29,B27,B25,B23,B21,B19,B17,B15,B13,B11,B9,B7B5=IF(ISTEXT(A4),IF(B4="PK","PK",IF(B4<0,B4*-1,B4*-1)),"")C5,C33,C31,C29,C27,C25,C23C5=IF(C4="W","L",IF(C4="L","W",IF(C4="PSH","PSH","")))


Yes. I wasn't sure how to use the MOD(ROW formula.
Thank you.


----------



## kevin9999 (Dec 30, 2022)

gtd526 said:


> Yes. I wasn't sure how to use the MOD(ROW formula.
> Thank you.


You're welcome 👍


----------



## gtd526 (Dec 30, 2022)

kevin9999 said:


> Does this do what you want?
> Book1ABCDEFGHIJKLM2Only "Home"  -  Odd rows only(A:A)Only "AWAY"  -  Even rows only(A:A)3TeamATSTeamWLPshTeamWLPsh4WAS-5LATL100ATL0005ORL5WBOS000BOS0006LAL6.5LBKN000BKN0007ATL-6.5WCHA000CHA0008PHX2WCHI010CHI0009TOR-2LCLE000CLE00010DET7WDAL000DAL00011CHI-7LDEN010DEN00012MIN6PSHDET000DET10013MIL-6PSHGSW010GSW00014PHI1LHOU000HOU00015NOP-1WIND000IND00016MIA3.5WLAC000LAC00017DEN-3.5LLAL000LAL01018POR-2.5WMEM000MEM00019GSW2.5LMIA000MIA10020UTA3WMIL001MIL00021SAC-3LMIN000MIN00122NOP100NOP00023  NYK000NYK00024OKC000OKC00025  ORL100ORL00026PHI000PHI01027  PHX000PHX10028POR000POR10029  SAC010SAC00030SAN000SAN00031  TOR010TOR00032UTA000UTA10033  WAS000WAS01034Sheet2Cell FormulasRangeFormulaF4:F33F4=SUMPRODUCT(--(MOD(ROW($A$4:$A$33),2)=1),--($A$4:$A$33=$E4),--($C$4:$C$33="W"))G4:G33G4=SUMPRODUCT(--(MOD(ROW($A$4:$A$33),2)=1),--($A$4:$A$33=$E4),--($C$4:$C$33="L"))H4:H33H4=SUMPRODUCT(--(MOD(ROW($A$4:$A$33),2)=1),--($A$4:$A$33=$E4),--($C$4:$C$33="PSH"))K4:K33K4=SUMPRODUCT(--(MOD(ROW($A$4:$A$33),2)=0),--($A$4:$A$33=$J4),--($C$4:$C$33="W"))L4:L33L4=SUMPRODUCT(--(MOD(ROW($A$4:$A$33),2)=0),--($A$4:$A$33=$J4),--($C$4:$C$33="L"))M4:M33M4=SUMPRODUCT(--(MOD(ROW($A$4:$A$33),2)=0),--($A$4:$A$33=$J4),--($C$4:$C$33="PSH"))B5,B33,B31,B29,B27,B25,B23,B21,B19,B17,B15,B13,B11,B9,B7B5=IF(ISTEXT(A4),IF(B4="PK","PK",IF(B4<0,B4*-1,B4*-1)),"")C5,C33,C31,C29,C27,C25,C23C5=IF(C4="W","L",IF(C4="L","W",IF(C4="PSH","PSH","")))


How could the formula be modified to show if the ATS(B:B) is (1) Less than -10, (2) between -5 and -9.5, (3) between -1 and -4.5, and (4) greater than 1?
This is to show the W/L using different Against The Spread ranges.


----------



## gtd526 (Dec 30, 2022)

gtd526 said:


> How could the formula be modified to show if the ATS(B:B) is (1) Less than -10, (2) between -5 and -9.5, (3) between -1 and -4.5, and (4) greater than 1?
> This is to show the W/L using different Against The Spread ranges.


Nevermind. I've modified the formula to show the changes. Thank you.


----------



## Peter_SSs (Dec 30, 2022)

gtd526 said:


> Yes. I wasn't sure how to use the MOD(ROW formula.
> Thank you.


The marked solution has been changed accordingly.
In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.


----------

