Excel Help please . . . (pretty please!)

[Guy Fawkes]

Normally have no troubles doing simplish spreadsheets for my betting accounts. However, i'm struggling for my latest venture . . .

I'm gogin to start a thread in GH forum soon with some football bets, i want a spread sheet to keep track of my bets, but more specificallu to calcuate the returns from a specific bet depending if it is a BACK or a LAY.

I know how have to take into account commision and can do this no probs, I'll try to explain as best I can . . .

Is it possible to say somethign like:

=IF(J10="WIN" AND G10 = "Back", *WORK OUT RETURNS FOR BACK BET*)

and likewise. . . .

=IF(J10="WIN" AND G10 = "Lay", *WORK OUT RETURNS FOR LAY BET*)

I can work out the returns bit (including minus commision) but am unsure as how I acn construct the above formula.



Any help would be much appreciated.

Thanks in advance.

[GotaFancy?]

I think you want:

=IF(AND(J10="WIN",G10 = "Back"), *WORK OUT RETURNS FOR BACK BET*)

and

=IF(AND(J10="WIN",G10 = "Lay"), *WORK OUT RETURNS FOR LAY BET*)

[Guy Fawkes]

Cheers mate

I'm struggling though - can I make the formula an 'either' . . . e.g. . . .

*work out back formula* OR *work out lay formula* . . . at the moment I'm putting them both in one cell and adding the lay formula to the back one - this is giving totslly wrong returns though.

[GotaFancy?]

you can do this:

=IF(AND(J10="WIN",G10 = "Back"),*WORK OUT RETURNS FOR BACK BET*,IF(AND(J10="WIN",G10 = "Lay"), *WORK OUT RETURNS FOR LAY BET*))

Think of it as If....Then.....Else

So

If ( Condition 1 is true, then do this, else do this)

by "nesting" the IF's, you are replacing the "else do this" with another condition.....so.....

If ( Condition 1 is true, then do this, else If ( Condition 2 is true, then do this, else do nothing))

The final "else do nothing" is implied and not actually included in your final (or first) formula.....

[Guy Fawkes]

Thanks so much mate, will give it a whirl when i've got a few minutes.

[GotaFancy?]

Just a further thought.....

If adding the 2 numbers together isn't giving the correct answer, then there must be an error in one of the original formulas - putting them together in one cell wont change this

By definition, both conditions cannot be true - so one of the values will always be zero (or null)....

I'd recommend you get it working with 2 cells before putting them together......

[georgej]

could it be you need to add the condition for a loss?

Totally agree with Gaf's breakdown of the formula though.

[Guy Fawkes]

Originally Posted by georgej

could it be you need to add the condition for a loss?

Could be, maybe, not sure, my excel skills aren't as good as I thought.

I know what i need to do, sort of know where i'm going wrong but don't have the language to do it.

Never mind, I'll just enter the returns in manually.

Thanks very much for your help though GaF, much appreciated.

[GotaFancy?]

If you were to manually fill in cells K10 to K13, what values would you put in? It shouldn't be too tricky to get this right......

[Valiant23]

Gaf?/George

Are you talking about the true/false commands in excel?

I'm not saying I know what I'm talking about but I have used that formula in similar calculations.

[georgej]

=IF(AND(J10="Win",G10="Back"),(I10*(H10-1)*0.95),IF(AND(J10="Win",G10="Lay"),((I10/1+H10)*0.95),-I10))

You'll need to check the sums - didn't know if you were betting to fixed profit or fixed liability. This should hopefully do it.

[Guy Fawkes]

Originally Posted by georgej

=IF(AND(J10="Win",G10="Back"),(I10*(H10-1)*0.95),IF(AND(J10="Win",G10="Lay"),((I10/1+H10)*0.95),-I10))

You'll need to check the sums - didn't know if you were betting to fixed profit or fixed liability. This should hopefully do it.

PM'd you mate.

[Guy Fawkes]

GotaFancy?



I salute you sir!

[GotaFancy?]

Hehe Glad it worked