How to calculate over or under based on dashed scores

[honkin]

Hi

I have a sheet which records scores and other data from football matches around the world. I needed to be able to work out a winner from scores listed like 0-1, 2-2, 3-0 and so on

One of the members here offered a perfect formula which works well

=IF(LEFT(A1,FIND("-",A1)-1)+0>MID(A1,FIND("-",A1)+1,99)+0,"W","L")

I just change the > to < or = or <> depending on what sort of results I am monitoring

That all works well for determining a win or a draw, but how would it be amended to look for under or over a certain of goals?

Say I had a model which was backing Over 2.5 goals in a match; how would this be changed so it put a W or L if the score was >2 or <3? Obviously >2 means it is 3 or more and <3 means it is 2 or less, so will cover the 2.5 goals scenario. I can then change it depending on whether it is 2.5 goals, or 3.5 or even 0.5

Thanks in advance

 

[jtakw]

And, in case for 2 digit scores, then:

I meant 2 or more digits scores...

 

[honkin]

thats pretty much what i posted

if the total goals are NOT greater than 2, then its a L

in my case , I used "below goal threshold" , so that part of the formula becomes L

I assume
0-3 = L
2-6 = L
as they are greater than 2 goals - BUT the right-hand-side scored more and so as convention they won

I also added an iferror for error handling, blank cells etc - IFERROR()

Book1
	A	B	C	D	E	F
1	Result	Goals	threshold coded	threshold 2	Win/lose
2	1-0	1	L	L	W
3	2-4	6	L	L	L
4	6-1	7	W	W	W
5	1-1	2	L	L	L
6	2-5	7	L	L	L
7	6-2	8	W	W	W
8	1-2	3	L	L	L
9	2-6	8	L	L	L
10	6-3	9	W	W	W
11	1-3	4	L	L	L
12						example
13	1-1	2	L	L	L	1-1 L
14	2-1	3	W	W	W	2-1 W
15	0-0	0	L	L	L	0-0 L
16	1-0	1	L	L	W	1-0 L
17	3-0	3	W	W	W	3-0 W
Sheet2

Cell Formulas
Range	Formula
B2:B17	B2	=IFERROR(LEFT(A2,FIND("-",A2)-1)+0+MID(A2,FIND("-",A2)+1,99)+0,"")
C2:C17	C2	=IFERROR(IF((LEFT(A2,FIND("-",A2)-1)+0+MID(A2,FIND("-",A2)+1,99)+0) >2, IF( LEFT(A2,FIND("-",A2)-1)+0>MID(A2,FIND("-",A2)+1,99)+0,"W","L"), "L"),"")
D2:D17	D2	=IFERROR(IF((LEFT(A2,FIND("-",A2)-1)+0+MID(A2,FIND("-",A2)+1,99)+0) >RIGHT(D$1,1)+0, IF( LEFT(A2,FIND("-",A2)-1)+0>MID(A2,FIND("-",A2)+1,99)+0,"W","L"), "L"),"")
E2:E17	E2	=IFERROR(IF(LEFT(A2,FIND("-",A2)-1)+0>MID(A2,FIND("-",A2)+1,99)+0,"W","L"),"")

cheers etaf, but yours uses a helper column, which is not what I wish to use when unnecessary. I merely want score and result

Ta

 

[honkin]

OK, keeping it simple, assuming single digit scores on either side, I think this should do what you want:

Book3.xlsx
	A	B
1	1-1	L
2	2-1	W
3	0-0	L
4	1-0	L
5	3-0	W
Sheet1075

Cell Formulas
Range	Formula
B1:B5	B1	=IF(LEFT(A1)+RIGHT(A1)>2,"W","L")

And, in case for 2 digit scores, then:

Book3.xlsx
	A	B
1	1-1	L
2	12-1	W
3	0-1	L
4	1-1	L
5	3-10	W
Sheet1075

Cell Formulas
Range	Formula
B1:B5	B1	=IF(LEFT(A1,FIND("-",A1)-1)+MID(A1,FIND("-",A1)+1,99)>2,"W","L")

That is exactly what I was after jtakw. Works like a charm

Thank you

 

[etaf]

Glad you have a solution

just for any one else reading
NO Helper columns used at all - as i said in my post
and you can see from the formula - it only references column A

I have laid out various columns, for viewing , so you can see the formulas

so column C is all you need

as shown again

I have a different result , as you have a W if its greater than 2 goals - regardless of which side won - I assume that is what you want
But just in case others are looking for a similar solution
for example
4-7
would be a lose , as its still over 2 goals - but the right hand side won

Book1
	A		C			F	G
1	Result		threshold coded
12						example
13	1-1		L			1-1 L	L
14	2-1		W			2-1 W	W
15	0-0		L			0-0 L	L
16	1-0		L			1-0 L	L
17	3-0		W			3-0 W	W
18	4-7		L				W
Sheet2

Cell Formulas
Range	Formula
C12:C18	C12	=IFERROR(IF((LEFT(A12,FIND("-",A12)-1)+0+MID(A12,FIND("-",A12)+1,99)+0) >2, IF( LEFT(A12,FIND("-",A12)-1)+0>MID(A12,FIND("-",A12)+1,99)+0,"W","L"), "L"),"")
G13:G18	G13	=IF(LEFT(A13,FIND("-",A13)-1)+MID(A13,FIND("-",A13)+1,99)>2,"W","L")

 

[jtakw]

That is exactly what I was after jtakw. Works like a charm

Thank you

You're welcome, thanks for the feedback.