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

 

[etaf]

is this the difference in the score or the total goals
assuming >3


1-0. not included.
3-0. not included as difference is 3 OR - as total goals are 3 , then not included
3-1 not included as difference is 2 OR - as total goals are 4 , then included
6-2 included as difference is 4 - OR - as total goals are 8 , then included

how do you get fractions of a goal

 

[honkin]

is this the difference in the score or the total goals
assuming >3


1-0. not included.
3-0. not included as difference is 3 OR - as total goals are 3 , then not included
3-1 not included as difference is 2 OR - as total goals are 4 , then included
6-2 included as difference is 4 - OR - as total goals are 8 , then included

how do you get fractions of a goal

cheers etaf
It is total goals, not difference. You don't get fractions of goals, it is just how bookmakers frame a market. Goals are always Over 0.5, Over 1.5, Over 2.5 etc. There are also Under markets of the same thing

So to have the formula calculate >2 goals would be the same thing as >2.5 goals, as the next possibility is 3 and so on

I just need to be able to alter that original formula so it can determine if the total goals scored is a win or a loss

cheers

 

[jtakw]

Hi,

I'm not quite following regarding your requirements, can you show some sample data with expected results ?

 

[etaf]

i'm also not 100% following, and examples would help

BUT , how about the following, if i have followed at all

this works out the total goals
LEFT(A2,FIND("-",A2)-1)+0+MID(A2,FIND("-",A2)+1,99)+0)
then you can hard code >2 or >3 <2 <3 etc
LEFT(A2,FIND("-",A2)-1)+0+MID(A2,FIND("-",A2)+1,99)+0) >2

Then , if thats True - use the original formula to work out a W or L

=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"), "below Goal Threshold")

you could have a title like
Threshold 2
and the formula work out the last number in this case 2
then

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

A = the score 1-2 , 4-5 etc

B = Total goals
=LEFT(A2,FIND("-",A2)-1)+0+MID(A2,FIND("-",A2)+1,99)+0

C = Threshold Coded , where we hardcode the number 2,3 etc - this example is 2
=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"), "below Goal Threshold")

D = Threshold 2 - where the title is used to get the threshold - in this example 2
=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"), "below Goal Threshold")

E = Win/Lose - your old formula , again just to see
=IF(LEFT(A2,FIND("-",A2)-1)+0>MID(A2,FIND("-",A2)+1,99)+0,"W","L")

Book1
	A	B	C	D	E
1	Result	Goals	threshold coded	threshold 2	Win/lose
2	1-0	1	below Goal Threshold	below Goal Threshold	W
3	2-4	6	L	L	L
4	6-1	7	W	W	W
5	1-1	2	below Goal Threshold	below Goal Threshold	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
Sheet2

Cell Formulas
Range	Formula
B2:B11	B2	=LEFT(A2,FIND("-",A2)-1)+0+MID(A2,FIND("-",A2)+1,99)+0
C2:C11	C2	=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"), "below Goal Threshold")
D2:D11	D2	=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"), "below Goal Threshold")
E2:E11	E2	=IF(LEFT(A2,FIND("-",A2)-1)+0>MID(A2,FIND("-",A2)+1,99)+0,"W","L")

 

[bebo021999]

Let's say team A & team B game score is in "a-b" format, the handycap is team A vs team B
for example, score: 2-0, handycap = 2.5, it means the actual score is : 2 - (0+2.5) = 2 - 2.5 ="Loss
3-1, handycap = 1.5, it means the actual score is : 3 - (1+1.5) = 3 - 2.5 ="Win"

With handycap = 2.5, the actual result is:

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

 

[honkin]

Let's say team A & team B game score is in "a-b" format, the handycap is team A vs team B
for example, score: 2-0, handycap = 2.5, it means the actual score is : 2 - (0+2.5) = 2 - 2.5 ="Loss
3-1, handycap = 1.5, it means the actual score is : 3 - (1+1.5) = 3 - 2.5 ="Win"

With handycap = 2.5, the actual result is:

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

Cheers bebo021999
I am not trying to do anything with handicaps, like Asian Handicaps, it is simply to determine if the combined score is > or < a particular number of goals; 2.5 for example
This formula doesn't work. If the score is 2-1, it should indicate a W, as it is over 2.5 goals, but it remains as L
Ta

 

[honkin]

Hi,

I'm not quite following regarding your requirements, can you show some sample data with expected results ?

Hi jtakw

OK, the examples are dead simple.

I'll just work it on Over 2.5 goals, as that one is pretty popular.

Scores - Result
1-1 L
2-1 W
0-0 L
1-0 L
3-0 W

So all I am trying to do is to have a W show if the combined score is over 2 (2.5 technically, but since over 2 means at least 3, it is fine)

Does that make sense?

The previous code works smashingly for W, L or Draw, as I just adjust the > to < or = or <> and that is fine. I was just struggling to have the same formula amended so it detected the total score and output a W or L result based on the number of goals.

I hope that helps

 

[etaf]

thats pretty much what i posted

I'll just work it on Over 2.5 goals, as that one is pretty popular.

Scores - Result
1-1 L
2-1 W
0-0 L
1-0 L
3-0 W

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"),"")

 

[jtakw]

Hi jtakw

OK, the examples are dead simple.

I'll just work it on Over 2.5 goals, as that one is pretty popular.

Scores - Result
1-1 L
2-1 W
0-0 L
1-0 L
3-0 W

So all I am trying to do is to have a W show if the combined score is over 2 (2.5 technically, but since over 2 means at least 3, it is fine)

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")