assuming its nested if's i need

im trying to get a next result on a sheet as in either a win, lose or draw based on the Percentage of games either won, drawn or lost but cant get it to compute, i know its something im doing wrong.......

Here is the formula im using.......



=IF(G5>H5+I5,"win",IF(H5>G5+I5,"draw",IF(I5>H5+G5,"lose")))

even tho we have lost more than we won it says next result would be a win .........

you can change the scores to see the knock on effect.....

Thank you for your help

Hi,

Replace H5 in the formula above with I5:

Formula:

=IF(G5>I5,"win",IF(I5>G5,"lose","draw"))

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' on your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

Having re-read this, I think I understand better what you want.

You want the likely result to be whatever the highest percentage of previous results is, I think. i.e. if you've had 2 wins, 5 draws and 1 loss, you should expect a draw; if 4 wins, 3 draws and 2 losses, a win; if 2 wins, 4 draws and 6 losses, a loss.
If that's correct, then what you want is to check what the highest percentage is in cells G8:I8 and return Win/Draw/Lose based on that.
This should do that:

Formula:

=IF(H8=MAX(G8:I8),"Draw",IF(G8=MAX(G8:I8),"Win",IF(I8=MAX(G8:I8),"Lose","??")))

The only problem with it is if all three percentages are the same, when it defaults to the first named result (i.e. 'Draw'). I think I can fix this with some AND/OR/something but I need to leave work now or my wife will kill me - I'll try to have a look later at home.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' on your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

Ok, this checks if the number (percentage) of wins is greater than both number of draws and losses, to return 'Win'. It then does the same for draws > wins & losses to give 'Draw' and for losses > wins & draws to give 'Lose'.
Because it's possible for the number of wins and draws to be the same, but both more than losses, etc, anything where the highest number (percentage) isn't unique returns 'too close to call'. Just replace "too close to call" with "" if you'd rather have a blank for that.

Formula:

=IF(AND(G8>H8,G8>I8),"Win",IF(AND(H8>G8,H8>I8),"Draw",IF(AND(I8>G8,I8>H8),"Lose","too close to call")))

If you want to cover all the options, this will do so:

Formula:

=IF(AND(G8>H8,G8>I8),"Win",IF(AND(H8>G8,H8>I8),"Draw",IF(AND(I8>G8,I8>H8),"Lose",IF(AND(G8=H8,G8>I8),"Win/Draw",IF(AND(G8=I8,G8>H8),"Win/Lose",IF(AND(G8<H8,H8=I8),"Draw/Lose",IF(AND(G8=H8,G8=I8),"Win/Draw/Lose")))))))

I hope that's the sort of thing you want?
If not, just ignore all of the above...!

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' on your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

I used this the formula below and changed "win/draw/lose" to "Unable to predict"
=IF(AND(G8>H8,G8>I8),"Win",IF(AND(H8>G8,H8>I8),"Draw",IF(AND(I8>G8,I8>H8),"Lose",IF(AND(G8=H8,G8>I8),"Win/Draw",IF(AND(G8=I8,G8>H8),"Win/Lose",IF(AND(G8<H8,H8=I8),"Draw/Lose",IF(AND(G8=H8,G8=I8),"Win/Draw/Lose")))))))

thank you all very much for all your help. ye are just lethal at excel without ye it wouldn't be possible to do what i need thank you all

You're very welcome and thanks for the rep.

Regards,
Aardigspook

your very welcome Aardigspook