gtd526
Well-known Member
- Joined
- Jul 30, 2013
- Messages
- 660
- Office Version
- 2019
- Platform
- Windows
Hello,
Looking for a correct value involving 4 different cell values.
Here is the worksheet.
A423 wants the correct answer, based on G424:G427 cell results.
If any cell in range (G424:G427) = a "L", then A423 is "L".
If any cell in range (G424:G427) = is "" or "W", then "pending".
If ALL cells in range (G424:G427) = "W", then "W".
Currently if any cell in range (G424:G427) is blank or not filled, its "pending".
Solution = only include in the range populated cells from H424:H427. 4 cells will always be the range, whether there blank of not.
I just want it to include cells being used, not necessarily the entire range.
The current formula works when the entire range is filled.
Thank you.
Looking for a correct value involving 4 different cell values.
Here is the worksheet.
A423 wants the correct answer, based on G424:G427 cell results.
If any cell in range (G424:G427) = a "L", then A423 is "L".
If any cell in range (G424:G427) = is "" or "W", then "pending".
If ALL cells in range (G424:G427) = "W", then "W".
Currently if any cell in range (G424:G427) is blank or not filled, its "pending".
Solution = only include in the range populated cells from H424:H427. 4 cells will always be the range, whether there blank of not.
I just want it to include cells being used, not necessarily the entire range.
The current formula works when the entire range is filled.
Thank you.
Wager Practice Macro.xlsm | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
423 | Pending | NFL | Parlay | $ 5.00 | $ 6.56 | ||||||||
424 | Parlay | W | DEN | JAX | * | ||||||||
425 | Parlay | W | LAR | CIN | * | ||||||||
426 | Parlay | 0 | |||||||||||
427 | Parlay | 0 | |||||||||||
428 | $ 13.65 | ||||||||||||
NFL |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A423 | A423 | =IF(COUNTIF($G424:$G427,"L")>0,"L",IF(COUNTIF($G424:$G427,0)>0,"Pending","W")) |
K423 | K423 | =IFERROR(IF($A423=0,$F423*-1,IF($A423="W",$G423,IF($A423="L",$F423*-1,""))),"") |
G424:G427 | G424 | =IFNA(INDEX('[NFL.xlsm]Weekly Picks'!$D$4:$D$35,MATCH($H424,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,FALSE)),0) |
H424:H427 | H424 | =IFERROR(LOOKUP(2, 1/((COUNTIF($H$423:$H423,'[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),"") |
I424:I427 | I424 | =IFERROR(IF(1=MOD(MATCH($H424,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0),2),INDEX('[NFL.xlsm]Weekly Picks'!$A$4:$A$35,MATCH($H424,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0)+1),INDEX('[NFL.xlsm]Weekly Picks'!$A$4:$A$35,MATCH($H424,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0)-1)),"") |
J424:J427 | J424 | =IFERROR(INDEX('[NFL.xlsm]Weekly Picks'!$F$4:$F$35,MATCH($H424,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,FALSE)),"") |
K428 | K428 | =SUM(K418:K427) |