Have "L"oss override "W"in or 0

[gtd526]

Hello,

I've added a formula for the W/L (G92 & 93) for each team and it shows 0 when there is no W or L. How can I get it to say "A Blank" (A91) when it shows 0 (G92, G93)?
The A91 formula works when there is no formula in G92 & G93.
Here is the data:

Wagers.xlsm
	A	B	C	D	E	F	G	H	I
91	W			NFL	Parlay	$ 5.00	$ 4.00
92							0	KC	CLE
93							0	LAR	CHI
NFL

Cell Formulas
Range	Formula
A91	A91	=IF(COUNTIF($G92:$G93,"L")>0, "L",IF(COUNTIF($G92:$G93,"")>0,"a blank","W"))
G92:G93	G92	=INDEX('C:\Users\gtd52\Documents\Sports-Wagering\[NFL.xlsm]Weekly Picks'!$E$4:$E$35,MATCH($H92,'C:\Users\gtd52\Documents\Sports-Wagering\[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0))
I92:I93	I92	=IF(1=MOD(MATCH(H92,'C:\Users\gtd52\Documents\Sports-Wagering\[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0),2),INDEX('C:\Users\gtd52\Documents\Sports-Wagering\[NFL.xlsm]Weekly Picks'!$A$4:$A$35,MATCH(H92,'C:\Users\gtd52\Documents\Sports-Wagering\[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0)+1),INDEX('C:\Users\gtd52\Documents\Sports-Wagering\[NFL.xlsm]Weekly Picks'!$A$4:$A$35,MATCH(H92,'C:\Users\gtd52\Documents\Sports-Wagering\[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0)-1))

 

[Fluff]

Are the values in Weekly Picks'!$E$4:$E$35 text or numbers?

 

[Bo_Ry]

Please try

=LOOKUP(9,FIND({0,"W","L"},G92&G93),{"a blank","W","L"})

or
=LOOKUP(2,1/COUNTIFS(G92:G93,{0,"W","L"}),{"a blank","W","L"})

 

[gtd526]

Are the values in Weekly Picks'!$E$4:$E$35 text or numbers?

I have them formatted at 'General'. ie "W"

 

[Fluff]

Have you tried Bo_Ry's suggestions?

 

[gtd526]

Please try

=LOOKUP(9,FIND({0,"W","L"},G92&G93),{"a blank","W","L"})

or
=LOOKUP(2,1/COUNTIFS(G92:G93,{0,"W","L"}),{"a blank","W","L"})

It works, but if only 1 is left blank, it should still read 'a blank' until all are filled. If there an "L", the "L" is final, so it overrides a "W".

This needs to wait until the second one is filled.

Wagers.xlsm
	A	B	C	D	E	F	G	H	I
91	W			NFL	Parlay	$ 5.00	$ 4.00
92							W	KC	CLE
93							0	LAR	CHI
NFL

Cell Formulas
Range	Formula
A91	A91	=LOOKUP(9,FIND({0,"W","L"},G92&G93),{"a blank","W","L"})
G92:G93	G92	=INDEX('[NFL.xlsm]Weekly Picks'!$E$4:$E$35,MATCH($H92,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0))
I92:I93	I92	=IF(1=MOD(MATCH(H92,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0),2),INDEX('[NFL.xlsm]Weekly Picks'!$A$4:$A$35,MATCH(H92,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0)+1),INDEX('[NFL.xlsm]Weekly Picks'!$A$4:$A$35,MATCH(H92,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0)-1))

This is correct, because "L" overrides "W".

Wagers.xlsm
	A	B	C	D	E	F	G	H	I
91	L			NFL	Parlay	$ 5.00	$ 4.00
92							W	KC	CLE
93							L	LAR	CHI
NFL

Cell Formulas
Range	Formula
A91	A91	=LOOKUP(9,FIND({0,"W","L"},G92&G93),{"a blank","W","L"})
G92:G93	G92	=INDEX('[NFL.xlsm]Weekly Picks'!$E$4:$E$35,MATCH($H92,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0))
I92:I93	I92	=IF(1=MOD(MATCH(H92,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0),2),INDEX('[NFL.xlsm]Weekly Picks'!$A$4:$A$35,MATCH(H92,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0)+1),INDEX('[NFL.xlsm]Weekly Picks'!$A$4:$A$35,MATCH(H92,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0)-1))

 

[gtd526]

Have you tried Bo_Ry's suggestions?

Yes. I'm doing it now.

 

[Fluff]

Ok, how about

=IF(COUNTIF($G92:$G93,0)>0, "a blank",IF(COUNTIF($G92:$G93,"L")>0,"L","W"))

 

[gtd526]

Ok, how about

=IF(COUNTIF($G92:$G93,0)>0, "a blank",IF(COUNTIF($G92:$G93,"L")>0,"L","W"))

It works, but if there's a "L" and 0, then "L" overrides ALL.

Wagers.xlsm
	A	B	C	D	E	F	G	H	I	J
91	a blank			NFL	Parlay	$ 5.00	$ 4.00
92							0	KC	CLE	*
93							L	LAR	CHI	*
NFL

Cell Formulas
Range	Formula
A91	A91	=IF(COUNTIF($G92:$G93,0)>0, "a blank",IF(COUNTIF($G92:$G93,"L")>0,"L","W"))
I92:I93	I92	=IF(1=MOD(MATCH(H92,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0),2),INDEX('[NFL.xlsm]Weekly Picks'!$A$4:$A$35,MATCH(H92,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0)+1),INDEX('[NFL.xlsm]Weekly Picks'!$A$4:$A$35,MATCH(H92,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0)-1))
J92:J93	J92	=INDEX('[NFL.xlsm]Weekly Picks'!$F$4:$F$35,MATCH($H92,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,FALSE))
G92:G93	G92	=INDEX('[NFL.xlsm]Weekly Picks'!$E$4:$E$35,MATCH($H92,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0))

Good-

Wagers.xlsm
	A	B	C	D	E	F	G	H	I	J
91	L			NFL	Parlay	$ 5.00	$ 4.00
92							W	KC	CLE	*
93							L	LAR	CHI	*
NFL

Cell Formulas
Range	Formula
A91	A91	=IF(COUNTIF($G92:$G93,0)>0, "a blank",IF(COUNTIF($G92:$G93,"L")>0,"L","W"))
I92:I93	I92	=IF(1=MOD(MATCH(H92,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0),2),INDEX('[NFL.xlsm]Weekly Picks'!$A$4:$A$35,MATCH(H92,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0)+1),INDEX('[NFL.xlsm]Weekly Picks'!$A$4:$A$35,MATCH(H92,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0)-1))
J92:J93	J92	=INDEX('[NFL.xlsm]Weekly Picks'!$F$4:$F$35,MATCH($H92,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,FALSE))
G92:G93	G92	=INDEX('[NFL.xlsm]Weekly Picks'!$E$4:$E$35,MATCH($H92,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0))

Good-

Wagers.xlsm
	A	B	C	D	E	F	G	H	I
91	W			NFL	Parlay	$ 5.00	$ 4.00
92							W	KC	CLE
93							W	LAR	CHI
NFL

Cell Formulas
Range	Formula
A91	A91	=IF(COUNTIF($G92:$G93,0)>0, "a blank",IF(COUNTIF($G92:$G93,"L")>0,"L","W"))
G92:G93	G92	=INDEX('[NFL.xlsm]Weekly Picks'!$E$4:$E$35,MATCH($H92,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0))
I92:I93	I92	=IF(1=MOD(MATCH(H92,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0),2),INDEX('[NFL.xlsm]Weekly Picks'!$A$4:$A$35,MATCH(H92,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0)+1),INDEX('[NFL.xlsm]Weekly Picks'!$A$4:$A$35,MATCH(H92,'[NFL.xlsm]Weekly Picks'!$A$4:$A$35,0)-1))

 

[Fluff]

In that case just swap the two countifs round.