gtd526
Well-known Member
- Joined
- Jul 30, 2013
- Messages
- 660
- Office Version
- 2019
- Platform
- Windows
Hello,
The formula in A:A works correctly, but when #N/A (G98:G99) is present, it results in "W", when it should be "a blank" until the fields are "W" or "L".
Thank you.
The formula in A:A works correctly, but when #N/A (G98:G99) is present, it results in "W", when it should be "a blank" until the fields are "W" or "L".
Thank you.
Cell Formulas | ||
---|---|---|
Range | Formula | |
A97 | A97 | =IF(COUNTIF($G98:$G99,"L")>0,"L",IF(COUNTIF($G98:$G99,0)>0,"a blank","W")) |
G98:G99 | G98 | =INDEX('[NFL.xlsm]Weekly Picks'!$E$4:$E$35,MATCH($H98,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,FALSE)) |
H98:H99 | H98 | =LOOKUP(2, 1/((COUNTIF($H$97:$H97,'[NFL.xlsm]Weekly Picks'!$H$4:$H$35)=0)*('[NFL.xlsm]Weekly Picks'!$H$4:$H$35<>"")*('[NFL.xlsm]Weekly Picks'!$F$4:$F$35="*")),'[NFL.xlsm]Weekly Picks'!$H$4:$H$35) |
I98:I99 | I98 | =IF(1=MOD(MATCH(H98,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0),2),INDEX('[NFL.xlsm]Weekly Picks'!$A$4:$A$35,MATCH(H98,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0)+1),INDEX('[NFL.xlsm]Weekly Picks'!$A$4:$A$35,MATCH(H98,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0)-1)) |
J98:J99 | J98 | =INDEX('[NFL.xlsm]Weekly Picks'!$F$4:$F$35,MATCH($H98,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,FALSE)) |