Simple if and question

M

muppet77

Board Regular
Joined
Jan 24, 2004
Messages
223
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.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
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:

Code: 
=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).

Code: 
=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:

Code: 
=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>
 
Last edited:
Upvote 0
Wow thanks!
Not near excel at the moment but could someone with a knowledge of asian handicaps test it please?
 
Last edited:
Upvote 0
[TABLE="width: 463"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]-3[/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3
[/TD]
[/TR]
[TR]
[TD="align: right"]-3[/TD]
[TD="align: center"]Tie[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss
[/TD]
[/TR]
[TR]
[TD="align: right"]-2.75[/TD]
[TD="align: center"]Half Win[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss
[/TD]
[/TR]
[TR]
[TD="align: right"]-2.5[/TD]
[TD="align: center"]Win[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[/TR]
[TR]
[TD="align: right"]-2.25[/TD]
[TD="align: center"]Full Win[/TD]
[TD="align: center"]Half Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[/TR]
[TR]
[TD="align: right"]-2[/TD]
[TD="align: center"]Win[/TD]
[TD="align: center"]Tie[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[/TR]
[TR]
[TD="align: right"]-1.75[/TD]
[TD="align: center"]Full Win[/TD]
[TD="align: center"]Half Win[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[/TR]
[TR]
[TD="align: right"]-1.5[/TD]
[TD="align: center"]Win[/TD]
[TD="align: center"]Win[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[/TR]
[TR]
[TD="align: right"]-1.25[/TD]
[TD="align: center"]Full Win[/TD]
[TD="align: center"]Full Win[/TD]
[TD="align: center"]Half Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[/TR]
[TR]
[TD="align: right"]-1[/TD]
[TD="align: center"]Win[/TD]
[TD="align: center"]Win[/TD]
[TD="align: center"]Tie[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[/TR]
[TR]
[TD="align: right"]-0.75[/TD]
[TD="align: center"]Full Win[/TD]
[TD="align: center"]Full Win[/TD]
[TD="align: center"]Half Win[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[/TR]
[TR]
[TD="align: right"]-0.5[/TD]
[TD="align: center"]Win[/TD]
[TD="align: center"]Win[/TD]
[TD="align: center"]Win[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[/TR]
[TR]
[TD="align: right"]-0.25[/TD]
[TD="align: center"]Full Win[/TD]
[TD="align: center"]Full Win[/TD]
[TD="align: center"]Full Win[/TD]
[TD="align: center"]Half Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Tie[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[/TR]
[TR]
[TD="align: right"]0.25[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Half Loss[/TD]
[TD="align: center"]Full Win[/TD]
[TD="align: center"]Full Win[/TD]
[TD="align: center"]Full Win[/TD]
[/TR]
[TR]
[TD="align: right"]0.5[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Win[/TD]
[TD="align: center"]Win[/TD]
[TD="align: center"]Win[/TD]
[/TR]
[TR]
[TD="align: right"]0.75[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Half Win[/TD]
[TD="align: center"]Full Win[/TD]
[TD="align: center"]Full Win[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Tie[/TD]
[TD="align: center"]Win[/TD]
[TD="align: center"]Win[/TD]
[/TR]
[TR]
[TD="align: right"]1.25[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Half Loss[/TD]
[TD="align: center"]Full Win[/TD]
[TD="align: center"]Full Win[/TD]
[/TR]
[TR]
[TD="align: right"]1.5[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Win[/TD]
[TD="align: center"]Win[/TD]
[/TR]
[TR]
[TD="align: right"]1.75[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Half Win[/TD]
[TD="align: center"]Full Win[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Tie[/TD]
[TD="align: center"]Win[/TD]
[/TR]
[TR]
[TD="align: right"]2.25[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Half Loss[/TD]
[TD="align: center"]Full Win[/TD]
[/TR]
[TR]
[TD="align: right"]2.5[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Win[/TD]
[/TR]
[TR]
[TD="align: right"]2.75[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Full Loss[/TD]
[TD="align: center"]Half Win[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Loss[/TD]
[TD="align: center"]Tie[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
European Roulette Bet Maker
Replies
3
Views
740
lrobbo314
lrobbo314
H
  • Solved
Calculation With Numbers Separated By Hyphen
Replies
9
Views
287
honkin
H
D
  • Question Question
Results by handicap - with ties. Look up by score without duplicates
Replies
4
Views
362
dkjonesau
D
K
  • Question Question
create fixtures but 2 vs 2
Replies
7
Views
151
kerm007
K
C
  • Question Question
Excel - Countif by Cell Value AND Cell Color
Replies
1
Views
596
awoohaw
awoohaw

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top