Complex IF Statement?

Basically I think I need a very complex IF statement to achieve my goal

Looking for a cell to display certain criteria (WIN, LOST, PUSH, 1/2 WIN 1/2 VOID, 1/2 VOID 1/2 LOST) based on numerical values

EXAMPLE:

In a soccer match if you bet Over 3 goals and there are 4 goals in the game you WIN. If there are 2 goals you LOSE. If there are exactly 3 you get your stake back, this is called PUSH

I need an IF statement that will allow me to display: (I have a slider with values that are increasing by 0.25)

Goals in game > Value in cell = WIN
Goals in game < Value in cell = LOST
Goals in game = Value in cell = PUSH

Thats the easy bit i think i.e 4 goals in a a game, Over 3 is WIN, 4 goals in a game. Over 4 is PUSH, etc

Now is where Im struggling...

You can bet on 3.25 goals in the game. If there are 3 your bet is 1/2 VOID 1/2 LOST. Think about it as when you have a decimal say 3.25, this really equals two values that are +/- 0.25 the slider value so 3.25 = 3 & 3.5.

1/2 of your bet goes on 3 and the other 1/2 goes on 3.5

Then this reverts back to the previous example where

Goals In Game 3, Slider 3 = Void (Push and void are the same thing so this can display void if needs be)
Goals In Game 3, Slider 3.5 = Lost

This creates 1/2 Void 1/2 Lost

EXAMPLES

GIG 3, Slider 3.5 = Lost
GIG 3, Slider 3.25 = 1/2 Void 1/2 Lost
GIG 3, Slider 3 = Void (Push)
GIG 3, Slider 2.75 = 1/2 Win 1/2 Void
GIG 3, Slider 2.5 = Win

GIG 2, Slider 3 = Lost
GIG 3, Slider 3 = Void
GIG 4, Slider 3 = Win
GIG 1, Slider 3 = Lost

Any help with this? (Very tricky...)

Thanks

If you don't mind it as a lookup table instead of an IF statement then the attached spreadsheet should work.

Ok Ive cracked it. Used link cells with the slider where I have added or subtracted 0.25 from the slider value so I now have two cells with values that makes it easier to meet the conditions.

Now I need to combine the following criteria into a statement

Win --- J8 > A11 AND J8 > B11

1/2 Win 1/2 Void --- J8 > A11 AND J8 = B11

Push ---J8 = B8

1/2 Void 1/2 Lost --- J8 = A11 AND J8 < B11

Lost --- J8 < A11 AND J8 < B11

Possible?

Originally Posted by Down Under

If you don't mind it as a lookup table instead of an IF statement then the attached spreadsheet should work.

This also works great by the way thanks! If the IF statement doesnt work this is just fine.

Out of curiosity, are you trying to look at how the IF statement would work for learning purposes, or are you just trying to avoid having to put in a table if possible? If it's the latter, you can code the lookup formula into a single cell statement without the table. Just put this into c2:

=LOOKUP(B2-A2,{-50,-0.25,0,0.25,0.5},{"Lost","Half Lost/Half Push","Push","Half Win/Half Push","Win"})

With the table at the moment it is not working for higher values such as 0 goals in a game Over 3. Will this formula fix that?

Yes it will. Alternatively, in the table where it says -0.50, change the value to -50.

Also out of curiosity is there an IF statement that will combine my criteria earlier?

I think this translates your Post #3 statements into the IF() function you're looking for:
=IF(AND(J8>A11,J8>B11),"Win",IF(AND(J8>A11,J8=B11),"1/2 Win 1/2 Void",IF(J8=B8,"Push",IF(AND(J8=A11,J8<B11),"1/2 Void 1/2 Lost","Lost"))))

Awesome thanks!

So I have done the Goal Line ok, now looking for Asian Handicap

Looking for a LOOKUP suitable to display the correct participants in this?

You are correct Current Score does not play a part with Goal Line IP (previous goals scored are taken into account no matter what).

This looks fantastic by the way I will test it some more but this looks exactly what I am after.

The cell in J is the GL scroller value, then the values you see in column C are that value *0.25

The cell in K is the total goals in the match added, but that was for my IF formula I dont believe it is relavent with the LOOKUP.

The cell in L is the AH scroller value but because you cant have negative values I subtracted 30 from the value and multipled this by 0.25. This displayed the fav handicaps. Conversely negating this new cell (C7) gives the dog handicaps. +0.25 etc

Thanks for all your help, like I said this looks spot on

No problems, thanks for explaining the scroller and the related cells. I honestly hadn't seen it before.

Just on the goal markets things, there's no need to put it in brackets like I said before given you are just talking total goals, so how it was should be fine. Given it's an absolute thing and not dependent on the score at the time of the bet it should be pretty simple.

I think I've got my head around what it is you are doing so if there are any errors just let me know and I should be able to correct them.

Yea correct score is simply there for the AH. So for goal line I can use the cell in K (which is final score total goals) and subtract the goal line as previous.

Really appreciate the time mate it is complicated I didnt know where to begin!