Simple if and question

[muppet77]

I am after a formula that tells me if a bet I have placed has won and in what way.
It is to do with Asian handicap betting. I shall explain more.

I have a bet in a1 that I have made.
It may be -1, which means that I have bet on the away team to win by 1 or more goals.
It could be a positive value. Eg +3 which means I think the home team will win by 3 or more.
If I bet +2 and the result is exactly +2 then the bet is refunded with no profit.

However it could also be a half, quarter or 3/4 value.

For example half a goal means that there cannot be a draw. So if I bet +2.5 or more, +3 will win but +2 will lose.
If I bet -4.5 and the result is -4, the bet loses and -5 would win.

A quarter goal bet means that half your bet is placed on a full goal bet an half is placed on the half goal bet. This means that you can win both halves, lose both halves or win one and lose the other.

A three quarter goal handicap also exists. This splits the bet between the half and full goal handicap.

A much better explanation can be found at
Asian Handicap Explained

So I have my bet in a1, and the result in b1.
A positive result means home team wins and a negative means away team wins with zero meaning a draw.

Can anyone help me write a formula for c1 that returns the result
Eg
Win
Lose
Void
Win half
Lose half

Thanks.

 

[jazzman831]

I'll take these one at a time. I'm going to assume you don't want VBA.

For a simple or half bet, you could use this:

=IF(A1=B1,"Tie",IF(AND(SIGN(A1)=SIGN(B1),ABS(B1)>ABS(A1)),"Win","Loss"))

(Note: I don't know how you treat a bet of 0).

Increasing it to a quarter bet is tougher, but doable. I think this formula now works for results of 0, but only for a bet of +/-.25 (I'm assuming that if you bet +/-.25, one bet is a loss and one is a tie).

=IF(ABS(A1)-INT(ABS(A1))=0.25,CHOOSE(IF((A1+0.25)=B1,0,IF(AND(IF((A1+0.25)=0,SIGN(A1),SIGN(A1+0.25))=SIGN(B1),ABS(B1)>ABS(A1+0.25)),1,-1))+IF((A1-0.25)=B1,0,IF(AND(IF((A1-0.25)=0,SIGN(A1),SIGN(A1-0.25))=SIGN(B1),ABS(B1)>ABS(A1-0.25)),1,-1))+3,"Full Loss","Half Loss","Push","Half Win","Full Win"),IF(A1=B1,"Tie",IF(AND(SIGN(A1)=SIGN(B1),ABS(B1)>ABS(A1)),"Win","Loss")))

And the 3/4 bets only get more complicated than that... it'd be a whole lot easier to use the first or second formula and manually make the bets you want to place. And really, now that I've done this all using Excel formulas, it'd be a butt-ton easier to do this in VBA as a UDF... at least you wouldn't have to repeat so many sections of the code. Hope this helps.

<edit> [edit] Actually on second look, the 3/4 bet might be really easy to add in there. I think it has the exact same logic as the quarter bet, correct? In which case, all you'd need to do is add a little bit to the front:

=IF(OR(ABS(A1)-INT(ABS(A1))=0.25,ABS(A1)-INT(ABS(A1))=0.75),CHOOSE(IF((A1+0.25)=B1,0,IF(AND(IF((A1+0.25)=0,SIGN(A1),SIGN(A1+0.25))=SIGN(B1),ABS(B1)>ABS(A1+0.25)),1,-1))+IF((A1-0.25)=B1,0,IF(AND(IF((A1-0.25)=0,SIGN(A1),SIGN(A1-0.25))=SIGN(B1),ABS(B1)>ABS(A1-0.25)),1,-1))+3,"Full Loss","Half Loss","Push","Half Win","Full Win"),IF(A1=B1,"Tie",IF(AND(SIGN(A1)=SIGN(B1),ABS(B1)>ABS(A1)),"Win","Loss")))

</edit>

 

[muppet77]

Wow thanks!
Not near excel at the moment but could someone with a knowledge of asian handicaps test it please?

 

[jazzman831]

	-3	-2	-1	0	1	2	3
-3	Tie	Loss	Loss	Loss	Loss	Loss	Loss
-2.75	Half Win	Full Loss	Full Loss	Full Loss	Full Loss	Full Loss	Full Loss
-2.5	Win	Loss	Loss	Loss	Loss	Loss	Loss
-2.25	Full Win	Half Loss	Full Loss	Full Loss	Full Loss	Full Loss	Full Loss
-2	Win	Tie	Loss	Loss	Loss	Loss	Loss
-1.75	Full Win	Half Win	Full Loss	Full Loss	Full Loss	Full Loss	Full Loss
-1.5	Win	Win	Loss	Loss	Loss	Loss	Loss
-1.25	Full Win	Full Win	Half Loss	Full Loss	Full Loss	Full Loss	Full Loss
-1	Win	Win	Tie	Loss	Loss	Loss	Loss
-0.75	Full Win	Full Win	Half Win	Full Loss	Full Loss	Full Loss	Full Loss
-0.5	Win	Win	Win	Loss	Loss	Loss	Loss
-0.25	Full Win	Full Win	Full Win	Half Loss	Full Loss	Full Loss	Full Loss
0	Loss	Loss	Loss	Tie	Loss	Loss	Loss
0.25	Full Loss	Full Loss	Full Loss	Half Loss	Full Win	Full Win	Full Win
0.5	Loss	Loss	Loss	Loss	Win	Win	Win
0.75	Full Loss	Full Loss	Full Loss	Full Loss	Half Win	Full Win	Full Win
1	Loss	Loss	Loss	Loss	Tie	Win	Win
1.25	Full Loss	Full Loss	Full Loss	Full Loss	Half Loss	Full Win	Full Win
1.5	Loss	Loss	Loss	Loss	Loss	Win	Win
1.75	Full Loss	Full Loss	Full Loss	Full Loss	Full Loss	Half Win	Full Win
2	Loss	Loss	Loss	Loss	Loss	Tie	Win
2.25	Full Loss	Full Loss	Full Loss	Full Loss	Full Loss	Half Loss	Full Win
2.5	Loss	Loss	Loss	Loss	Loss	Loss	Win
2.75	Full Loss	Full Loss	Full Loss	Full Loss	Full Loss	Full Loss	Half Win
3	Loss	Loss	Loss	Loss	Loss	Loss	Tie

<tbody>
</tbody>

 

[muppet77]

Wow. As far as I can see that looks spot on.
Fantastic job. Many thanks.

 

[jazzman831]

Yay! Glad I could be of service.