Find zero in double number cell

[honkin]

I have a column which has football scores in it. They are input as 2 digits and then formatted to contain a dash using the 0-0 format; as an example, 2-0 is input as 20, 4-1 is input as 41 and so on. The next column is to automate the result, so if there is a zero in the score anywhere, the result would be L and if there is not a zero, it is W
I tried using a wildcard to indicate 0* or *0, but have not been able to get it working correctly. This is what I tried =IF(OR(N2=*0, N2=0*), "L", "W")
So how does one indicate that a zero followed by any number or any number followed by zero is L, if both are false, then it is a W?
Thanks in advance

 

[Andrew Fergus]

There are a couple of issues here. The score 0-0 is actually stored as 0. So there is no leading or trailing zero - it is simply zero. Select a cell with 0-0 to see how Excel stored that value. You can amend your formula to just test for the value 0 but I do not recommend that. The two scores for 1 game result should be in separate cells, not combined into a single cell. Using your current setup, how does it handle scores such as 0-12, or 10-11? I'm guessing you are seeing unintended results. I recommend split the scores for each game into separate columns.

 

[Peter_SSs]

if there is a zero in the score anywhere, the result would be L and if there is not a zero, it is W

That seems a bit odd to me and I don't disagree with Andrew's comments, but if you insist on the current format, and given the description above, what about this?

24 04 01.xlsm
	N	O
1
2	2-0	L
3	4-1	W
4	0-2	L
5	2-0	L
6	2-2	W
7	1-3	W
8	3-3	W
WL

Cell Formulas
Range	Formula
O2:O8	O2	=IF(ISNUMBER(FIND(0,TEXT(N2,"00"))),"L","W")

 

[Andrew Fergus]

Good to see you Peter.Did you see this part?

"They are input as 2 digits and then formatted to contain a dash using the 0-0 format"

The current setup stores input of 00 as 0, but displays it as 0-0. Whilst your solution works for single digit scores, it all gets a bit weird with scores of say 1-10 and 10-11 etc.

 

[Peter_SSs]

Hi Andrew
Good to see you back too!!

Did you see this part?

Yes I did. It specifically says the they are input as 2-digit numbers. If that is correct 1-10 and 10-11 will not occur as they would not be 2-digit numbers.

 

[Andrew Fergus]

Thanks for that.

Nice catch. I was looking for the unstated assumption but it could be games are stopped once a team reaches a score of 9 so that blows away my assumption.....

 

[honkin]

There are a couple of issues here. The score 0-0 is actually stored as 0. So there is no leading or trailing zero - it is simply zero. Select a cell with 0-0 to see how Excel stored that value. You can amend your formula to just test for the value 0 but I do not recommend that. The two scores for 1 game result should be in separate cells, not combined into a single cell. Using your current setup, how does it handle scores such as 0-12, or 10-11? I'm guessing you are seeing unintended results. I recommend split the scores for each game into separate columns.

Cheers Andrew. Yes, you are correct...when I select a cell with 0-0, it shows as simply 0

 

[honkin]

Hi Andrew
Good to see you back too!!


Yes I did. It specifically says the they are input as 2-digit numbers. If that is correct 1-10 and 10-11 will not occur as they would not be 2-digit numbers.

That seems a bit odd to me and I don't disagree with Andrew's comments, but if you insist on the current format, and given the description above, what about this?

24 04 01.xlsm
	N	O
1
2	2-0	L
3	4-1	W
4	0-2	L
5	2-0	L
6	2-2	W
7	1-3	W
8	3-3	W
WL

Cell Formulas
Range	Formula
O2:O8	O2	=IF(ISNUMBER(FIND(0,TEXT(N2,"00"))),"L","W")

That seems to be exactly what I'm after. I fully get what Andrew was indicating, but the chances of a 3 digit score are very slim and this seems to do the job. Thanks very much for the effort

 

[Peter_SSs]

That seems to be exactly what I'm after.

You're welcome. Thanks for the follow-up.

 

[Scott Huish]

Just throwing this out as a possibility:

Book1
	N	O
2	2-0	L
3	4-1	W
4	0-2	L
5	2-0	L
6	2-2	W
7	1-3	W
8	3-3	W
Sheet1

Cell Formulas
Range	Formula
O2:O8	O2	=IF((N2>10)*RIGHT(N2),"W","L")