Formula for win or lose as text to result in different mathematical outcomes

**JBJames** · 11-19-2009, 11:43 AM

Hi all

First time poster but a problem i've been wrapping my brain around for a bit! Sorry if the title was rubbish!

I don't think it's complicated but here goes:

I like to keep stats of all my winnings when gambling in a spreadsheet.

My columes are as follows:

Odds, Stake, Won or Lost, Start Bank, End Bank

What I'm trying to do is have the end bank formula be something like:

If Won the bet then it would take the stake minus 5% and all it to the Start Bank

But also if lost the bet then would times the Stake by the odds and minus it from the Start Bank

I've attached an example sheet to have a look at.

Would greatly appreciate any help or advice!

Cheers all
James

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

Perhaps you want:

=SUM(D2,B2*IF(C2="Won",0.95,-A2))

**JBJames** · 11-19-2009, 11:58 AM

Yeah that works nicely for when the outcome is a win.

Is there anyway of having an OR option.

For example

Win the first so can modify that formula for all win situations

And have in the same possible situation the eventuality that a loss occurs and have to multiple the stake by the odds?

Thanks for your quick reply btw!

**DonkeyOte** · 11-19-2009, 12:01 PM

Originally Posted by JBJames

Yeah that works nicely for when the outcome is a win.

The formula provided calculates Win & Loss exactly as requested, ie applied to your earlier file in cell E2 (copied down) it will generate the same values as those in your sample file - ie 1095,851,946

Re:

Originally Posted by JBJames

Win the first so can modify that formula for all win situations

I'm afraid I don't follow, perhaps post a further more detailed example ?

**JBJames** · 11-19-2009, 12:04 PM

Sorry my ignorance got the better of me. That works absolutely brilliantly so thank you very much!

**JBJames** · 11-19-2009, 06:48 PM

Sorry been playing with this again and not quite got it!

The above formula works brilliants if the "won" is applicable.

Was wondering what I'd have to do for an one or the other situation

For example:

If Won then the above formula.

But if it's a Loss then it would be the amount staked, multiplied by the odds minus one. So that on it's own would be =sum(Start bank - ((stake)-(bet*(odds-1))

Cheers for any further help!

Sorry if it's total rubbish!