W/L record, Home or Away

gtd526

gtd526

Well-known Member
Joined
Jul 30, 2013
Messages
657
Office Version
  1. 2019
Platform
  1. Windows
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.xlsm
ABCDEFGHIJKLM
2Only "Home" - Odd rows only(A:A)Only "AWAY" - Even rows only(A:A)
3TeamATSTeamWLPshTeamWLPsh
4WAS-5LATL100ATL100
5ORL5WBOS000BOS000
6LAL6.5LBKN000BKN000
7ATL-6.5WCHA000CHA000
8PHX2WCHI010CHI010
9TOR-2LCLE000CLE000
10DET7WDAL000DAL000
11CHI-7LDEN010DEN010
12MIN6PSHDET100DET100
13MIL-6PSHGSW010GSW010
14PHI1LHOU000HOU000
15NOP-1WIND000IND000
16MIA3.5WLAC000LAC000
17DEN-3.5LLAL010LAL010
18POR-2.5WMEM000MEM000
19GSW2.5LMIA100MIA100
20UTA3WMIL0.50.51MIL0.50.51
21SAC-3LMIN0.50.51MIN0.50.51
22NOP100NOP100
23  NYK000NYK000
24OKC000OKC000
25  ORL100ORL100
26PHI010PHI010
27  PHX100PHX100
28POR100POR100
29  SAC010SAC010
30SAN000SAN000
31  TOR010TOR010
32UTA100UTA100
33  WAS010WAS010
Sheet1
Cell Formulas
RangeFormula
F4: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 Formatting
CellConditionCell FormatStop If True
A4:A33Expression=AND($AL4>=$AL$1,$AL4<>"")textNO
B4:C11Cellcontains a blank value textNO
C4:C33Cell Value="psh"textNO
C4:C33Cell Value="W"textNO
C4:C33Cell Value="L"textNO
A4:A33Expression=NOT(ISERROR(MATCH($A4,$G$52:$G$66,0)))textNO
B4:B33Cell Value<0textNO
B4:B33Expression=B4<=-10textNO
B4:B33Expression=AND($A4=$AE4,$B4>0,$A4<>"",$AL4<=$AL$1)textNO
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
Does this do what you want?
Book1
ABCDEFGHIJKLM
2Only "Home" - Odd rows only(A:A)Only "AWAY" - Even rows only(A:A)
3TeamATSTeamWLPshTeamWLPsh
4WAS-5LATL100ATL000
5ORL5WBOS000BOS000
6LAL6.5LBKN000BKN000
7ATL-6.5WCHA000CHA000
8PHX2WCHI010CHI000
9TOR-2LCLE000CLE000
10DET7WDAL000DAL000
11CHI-7LDEN010DEN000
12MIN6PSHDET000DET100
13MIL-6PSHGSW010GSW000
14PHI1LHOU000HOU000
15NOP-1WIND000IND000
16MIA3.5WLAC000LAC000
17DEN-3.5LLAL000LAL010
18POR-2.5WMEM000MEM000
19GSW2.5LMIA000MIA100
20UTA3WMIL001MIL000
21SAC-3LMIN000MIN001
22NOP100NOP000
23  NYK000NYK000
24OKC000OKC000
25  ORL100ORL000
26PHI000PHI010
27  PHX000PHX100
28POR000POR100
29  SAC010SAC000
30SAN000SAN000
31  TOR010TOR000
32UTA000UTA100
33  WAS000WAS010
34
Sheet2
Cell Formulas
RangeFormula
F4: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","")))
 
Upvote 0
Solution
kevin9999 said:
Does this do what you want?
Book1
ABCDEFGHIJKLM
2Only "Home" - Odd rows only(A:A)Only "AWAY" - Even rows only(A:A)
3TeamATSTeamWLPshTeamWLPsh
4WAS-5LATL100ATL000
5ORL5WBOS000BOS000
6LAL6.5LBKN000BKN000
7ATL-6.5WCHA000CHA000
8PHX2WCHI010CHI000
9TOR-2LCLE000CLE000
10DET7WDAL000DAL000
11CHI-7LDEN010DEN000
12MIN6PSHDET000DET100
13MIL-6PSHGSW010GSW000
14PHI1LHOU000HOU000
15NOP-1WIND000IND000
16MIA3.5WLAC000LAC000
17DEN-3.5LLAL000LAL010
18POR-2.5WMEM000MEM000
19GSW2.5LMIA000MIA100
20UTA3WMIL001MIL000
21SAC-3LMIN000MIN001
22NOP100NOP000
23  NYK000NYK000
24OKC000OKC000
25  ORL100ORL000
26PHI000PHI010
27  PHX000PHX100
28POR000POR100
29  SAC010SAC000
30SAN000SAN000
31  TOR010TOR000
32UTA000UTA100
33  WAS000WAS010
34
Sheet2
Cell Formulas
RangeFormula
F4: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","")))
Click to expand...
Yes. I wasn't sure how to use the MOD(ROW formula.
Thank you.
 
Upvote 0
kevin9999 said:
Does this do what you want?
Book1
ABCDEFGHIJKLM
2Only "Home" - Odd rows only(A:A)Only "AWAY" - Even rows only(A:A)
3TeamATSTeamWLPshTeamWLPsh
4WAS-5LATL100ATL000
5ORL5WBOS000BOS000
6LAL6.5LBKN000BKN000
7ATL-6.5WCHA000CHA000
8PHX2WCHI010CHI000
9TOR-2LCLE000CLE000
10DET7WDAL000DAL000
11CHI-7LDEN010DEN000
12MIN6PSHDET000DET100
13MIL-6PSHGSW010GSW000
14PHI1LHOU000HOU000
15NOP-1WIND000IND000
16MIA3.5WLAC000LAC000
17DEN-3.5LLAL000LAL010
18POR-2.5WMEM000MEM000
19GSW2.5LMIA000MIA100
20UTA3WMIL001MIL000
21SAC-3LMIN000MIN001
22NOP100NOP000
23  NYK000NYK000
24OKC000OKC000
25  ORL100ORL000
26PHI000PHI010
27  PHX000PHX100
28POR000POR100
29  SAC010SAC000
30SAN000SAN000
31  TOR010TOR000
32UTA000UTA100
33  WAS000WAS010
34
Sheet2
Cell Formulas
RangeFormula
F4: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","")))
Click to expand...
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.
 
Upvote 0
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.
Click to expand...
Nevermind. I've modified the formula to show the changes. Thank you.
 
Upvote 0
gtd526 said:
Yes. I wasn't sure how to use the MOD(ROW formula.
Thank you.
Click to expand...
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

gtd526
  • Question
convert cell value to formula
Replies
8
Views
174
Fluff
Fluff
gtd526
  • Question
macro to add correct number of ")" at end of nested IF statement
Replies
1
Views
128
gtd526
gtd526
gtd526
  • Question
vba Sorting Columns w/ 2 Criteria's
Replies
1
Views
137
gtd526
gtd526
SaraWitch
  • Solved
Count number of cells over 18 but discount dates
Replies
3
Views
153
Fluff
Fluff
gtd526
  • Question
vba selecting a specific range if ISNUMBER
Replies
1
Views
159
gtd526
gtd526

Forum statistics

Threads
1,215,012
Messages
6,122,682
Members
449,091
Latest member
peppernaut

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