Using 3 cell choices resulting in 1 overall choice

[gtd526]

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.

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)

 

[Fluff]

How about

=IF(COUNTIF(G424:G427,"l"),"L",IF(COUNTIF(G424:G427,"w")=4,"W","Pending"))

 

[gtd526]

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.

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)

No, its still giving me "Pending".

Wager Practice Macro.xlsm
	A	B	C	D	E	F	G
423	Pending			NFL	Parlay	$ 5.00	$ 6.56
424					Parlay		W
425					Parlay		W
426					Parlay		0
427					Parlay		0
NFL

Cell Formulas
Range	Formula
A423	A423	=IF(COUNTIF(G424:G427,"1"),"L",IF(COUNTIF(G424:G427,"w")=4,"W","Pending"))
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)

 

[Fluff]

The 1st countif should have an L in it, not a 1.
Also from your description it should be pending as there are no Ls & only 2Ws

 

[gtd526]

The 1st countif should have an L in it, not a 1.
Also from your description it should be pending as there are no Ls & only 2Ws

I replace the 1 with L.
There are 4 cells in the Range (G424:G427), but only 2 are filled. Its 'Pending' because the other 2 are not filled (I assume).
But those cells will never be filled.
Is there a way to only use the filled cells of the 4?
The H:H column could be filled, using that as a guide of what will be filled in G:G.

Wager Practice Macro.xlsm
	A	B	C	D	E	F	G	H
423	Pending			NFL	Parlay	$ 5.00	$ 6.56
424					Parlay		W	DEN
425					Parlay		W	LAR
426					Parlay		0
427					Parlay		0
NFL

Cell Formulas
Range	Formula
A423	A423	=IF(COUNTIF(G424:G427,"L"),"L",IF(COUNTIF(G424:G427,"w")=4,"W","Pending"))
G424:G427	G424	=IFNA(INDEX('C:\Users\gtd52\Documents\Sports-Wagering\[NFL.xlsm]Weekly Picks'!$D$4:$D$35,MATCH($H424,'C:\Users\gtd52\Documents\Sports-Wagering\[NFL.xlsm]Weekly Picks'!$A$4:$A$35,FALSE)),0)
H424:H427	H424	=IFERROR(LOOKUP(2, 1/((COUNTIF($H$423:$H423,'C:\Users\gtd52\Documents\Sports-Wagering\[NFL.xlsm]Weekly Picks'!$H$4:$H$35)=0)*('C:\Users\gtd52\Documents\Sports-Wagering\[NFL.xlsm]Weekly Picks'!$H$4:$H$35<>"")*('C:\Users\gtd52\Documents\Sports-Wagering\[NFL.xlsm]Weekly Picks'!$F$4:$F$35="*")),'C:\Users\gtd52\Documents\Sports-Wagering\[NFL.xlsm]Weekly Picks'!$H$4:$H$35),"")

 

[Fluff]

You originally said

If ALL cells in range (G424:G427) = "W", then "W".

and

If any cell in range (G424:G427) = is "" or "W", then "pending".

As G426:G427 are "" then surely it should be pending?

 

[gtd526]

You originally said

and

As G426:G427 are "" then surely it should be pending?

Yes, but it should only include G424:G425 since H424:H425 are populated.
I will use the entire range (of 4) to start but will be defined by the population of H:H column (2).

 

[Fluff]

Ok try

=IF(COUNTIF(G424:G427,"L"),"L",IF(COUNTIF(G424:G427,"w")=COUNTIF(H424:H427,"?*"),"W","Pending"))

 

[gtd526]

Ok try

=IF(COUNTIF(G424:G427,"L"),"L",IF(COUNTIF(G424:G427,"w")=COUNTIF(H424:H427,"?*"),"W","Pending"))

Perfect. Thank you.

 

[Fluff]

You're welcome & thanks for the feedback.