# Solved: Nested IF's and AND's

Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

#### biglot

Thread Starter
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

Chuck
Moderator
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

Thread Starter
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

Thread Starter
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

Chuck
Moderator
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

Thread Starter
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

Chuck
Moderator
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.

#### Attachments

• 14.5 KB Views: 198

#### biglot

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

#### MRdNk

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

#### cwwozniak

Chuck
Moderator
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

Thread Starter
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

Chuck
Moderator
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

Thread Starter
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!

Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

As Seen On

### Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

over 807,865 other people just like you!