How to express multiple IF Statement

**ddub25** · 11-04-2009, 11:25 AM

Hello

I have attached a file showing a few simplified rows of my sports betting spreadsheet. In column N I have a formula which calculates the profit returned from each bet considering whether the bet was a 'win', 'lose' or 'void' and whether any commision will be charged.

The formula takes into account whether the bet was placed with BF or BQ, and if so minuses 5% commision from the profit. If the bet was placed with any other bookmaker, no commision is minused.

My problem is trying to express the formula to include 'win 0.5', 'lose 0.5' and 'draw'. On the 3rd bet example where the bet won half profit, the profit should be £36. I have manually entered this figure because everytime I try to express the formula to incorporate 'win 0.5' it says formula error. I have the same problem trying to incorporate 'lose 0.5' and 'draw'.

If anyone can help with this formula it would be much appreciated. Thanks in advance.

Dan

**mewingkitty** · 11-04-2009, 11:37 AM

Sounds like something you could do with cell formatting...
Set your minus to read "loss" then the number
your positive to read "gain" then the number
and 0 to read as "draw" ?

**ddub25** · 11-05-2009, 06:29 AM

I don't know too much about cell formatting. Does anyone know of a good online resource where I can learn more about the basic principles of consctructing more advanced formulas.

Here is the formula that I want to adapt:

=IF((K3="win"),IF(H3="BF",0.95,IF(H3="BQ",0.95,1))*(A3*G3-G3),IF(K3="lose",G3-(G3*2),G3-G3))

The 'void' and 'draw' results are taken care of with the ',G3-G3))' part at the end of the formula. It is the just the 'win 0.5' and the 'lose 0.5' that I am struggling to incorporate. Thanks.

Dan

**DonkeyOte** · 11-05-2009, 07:17 AM

Dan, I don't think formatting will help here.

Using your existing setup

Please Login or Register  to view this content.

**ddub25** · 11-05-2009, 11:14 AM

Thanks Donkeyote, that's brilliant; solved my problem perfectly. Now I just have to get my head round how the formula works. Don't suppose you know of a good resource on the internet for learning how to construct more advanced Excel formulas? Thanks.

Dan

**DonkeyOte** · 11-05-2009, 11:19 AM

I'm self taught - primarily trial and error with a good dollop of forum assistance.

Re the formula - pretty much does the same as you had initially (give or take the odd syntax change) but with two notable exceptions

1 - rather than look exclusively for Win/Lose you're now looking for phrases that start with Win or Lose - thus Win and Win 0.5 use the same part of the IF (as does Lose / Lose 0.5)

2 - take the final result and divide by 1 or 2 ... 2 where the phrase ends in 0.5
we create the 1/2 by taking 1 and adding a Boolean output (ends in 0.5) which when coerced will become 0 (false) or 1 (true) ... so where we end in 0.5 we have a divisor of 2 (ie we halve our output).