Solved: Nested IF's and AND's

[biglot]

Hi ...

I have an Excel question, if you don't mind.

I can't get the following to work. Can anyone see why not?

=IF(AND(F3=1,E3>2.1,E3<5.1),$J$2,0-(E3-1)*$J$1,IF(AND(F3=1,E3>5,E3<7.1),$K$2,0-(E3-1)*$K$1,IF(AND(F3=1,E3>7,E3<9.1),$L$2,0-(E3-1)*$L$1,IF(AND(F3=1,E3>9,E3<12.1,$M$2,0-(E3-1)*$M$1,IF(AND(F3=1,E3>12,E3<20.1),$N$2,0-(E3-1)*$N$1)))))

Where,

J1=100, J2=95
K1=75, K2=71.25
L1=50, L2=47.50
M1=30, M2=28.50
N1=15, N2=14.75

Thanks in advance ...

 

[cwwozniak]

Hi biglot and welcome to TSG.

The logical IF function uses exactly two or three arguments, no more.
IF(logical_test,value_if_true,value_if_false)

In your case:
logical_test is AND(F3=1,E3>2.1,E3<5.1)
value_if_true is $J$2
value_if_false is 0-(E3-1)*$J$1

The only thing you can have after 0-(E3-1)*$J$1 is a closing parentheses. You can not add another IF function, or anything else, as another argument.

Since I am not sure what you are trying to do, I can't even start to suggest how to fix your formula. Could you maybe describe in detail what you are trying to do in the formula?

 

[biglot]

Chuck,

This formula is for use in a horse racing spreadsheet, where F3=1 indicates a win for the system, and F3=0 indicates a loss. The decimal odds are entered in E3.

The figures,

2.20 - 5.00: J1=100, J2=95
5.00 - 7.00: K1=75, K2=71.25
7.00 - 9.00: L1=50, L2=47.50
9.00 - 12.00: M1=30, M2=28.50
12.00 - 20.00 : N1=15, N2=14.75

represent the decimal price, or odds ranges and betting amounts, to be won, or lost.

So, with the odds ranging 2.1 - 5.0, a win, indicated by 1 typed into F3, shows J2, or $95.00 in I3.

F3=0, and I3 would register 0-(E3-1)*J1, or -$200.00. J1 value, $100.00.


... biglot

 

[biglot]

Chuck, To clarify, if odds of 3.00 were entered into E3, and F3=0, I3 would register 0-(E3-1)*J1, or -$200.00. J1 value, $100.00. ... biglot

 

[cwwozniak]

The figures,

2.20 - 5.00: J1=100, J2=95
5.00 - 7.00: K1=75, K2=71.25

...

So, with the odds ranging 2.1 - 5.0,
...

Is the range 2.20 to 5.00 or 2.10 to 5.00?

If E3 is equal to exactly 5.00, should J1 and J2 be used in the calculations or K1 and K2?
The same question applies to the other odds breakpoints as well.

What should happen if E3 is below the lowest limit; say E3 is set to 2.00?

What should happen if E3 is greater than 20.00?

FWiW, part of the formula for I3 might have been to easier to create if the odds range, loss amounts and win amounts were in a table, then a VLOOKUP function could be used. It should still be possible to use a more complex formula with nested IF functions.

I will not have time to try any corrections until much later today. Maybe someone else here will come up with the correct formula before then.

 

[biglot]

Odds in this section to be 2.20 - 5.00. If E3 is equal to exactly 5.00, J1 and J2 to be used in the calculations. In this system, races not bet on where odds are below 2.20, or above 20.00. When I enter individual section of the overall statement, results show as they should. Thanks again.

 

[cwwozniak]

See if the attached Excel file works as expected.

I added input number range validation for the odds and Won/Lost entries and added conditional formatting to I3 to make it red for negative numbers.

 

[biglot]

Just what the doctor ordered! Thank you very much!!

 

[MRdNk]

Hi biglot please mark as Solved, see Top of Thread.

 

[cwwozniak]

Thank you very much!!

You are very welcome. Do I get a cut of the winnings?

As MRdNk mentioned, you, as the original poster, should mark this tread as being solved.

 

[biglot]

If you're familiar with Betfair, you'll recognize this to be a 'laying' system, one that bets on horses to lose. I intend to monitor the results through until next spring before putting any more money into my Betfair account.

 

[cwwozniak]

one that bets on horses to lose.

I thought that the calculations for the winners and losers seemed a bit backwards. Now I understand why.

I guess I'm not a punter. Never heard of Betfair until just now and Googled them..

 

[biglot]

At first blush, you'd think betting against horses to lose is a snap. But it's not. It carries huge risk. But dig around the Betfair universe. It's an incredible place, a stock exchange-like place for gamblers. In fact, guys with stock market knowledge have transferred their stock market programming skills to the gambling world. (Like there's that much of a difference to begin with?) And these horse betting systems? Backing to win, or Laying to lose, there must be millions of them!!

 

[MRdNk]

A bit more stable then the stock market at the moment too!