1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Nested IF's and AND's

Discussion in 'Archive: Business Applications' started by biglot, Sep 26, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. biglot

    biglot Thread Starter

    Joined:
    Sep 26, 2008
    Messages:
    7
    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 ...
     
    biglot, Sep 26, 2008
    #1
  3. cwwozniak

    cwwozniak Trusted Advisor Spam Fighter

    Joined:
    Nov 28, 2005
    Messages:
    63,004
    First Name:
    Chuck
    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?
     
    cwwozniak, Sep 26, 2008
    #2
  4. biglot

    biglot Thread Starter

    Joined:
    Sep 26, 2008
    Messages:
    7
    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, Sep 26, 2008
    #3
  5. biglot

    biglot Thread Starter

    Joined:
    Sep 26, 2008
    Messages:
    7
    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
     
    biglot, Sep 26, 2008
    #4
  6. cwwozniak

    cwwozniak Trusted Advisor Spam Fighter

    Joined:
    Nov 28, 2005
    Messages:
    63,004
    First Name:
    Chuck
    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.
     
    cwwozniak, Sep 26, 2008
    #5
  7. biglot

    biglot Thread Starter

    Joined:
    Sep 26, 2008
    Messages:
    7
    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.
     
    biglot, Sep 27, 2008
    #6
  8. cwwozniak

    cwwozniak Trusted Advisor Spam Fighter

    Joined:
    Nov 28, 2005
    Messages:
    63,004
    First Name:
    Chuck
    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.
     

    Attached Files:

    cwwozniak, Sep 27, 2008
    #7
  9. biglot

    biglot Thread Starter

    Joined:
    Sep 26, 2008
    Messages:
    7
    Just what the doctor ordered! Thank you very much!!
     
    biglot, Sep 28, 2008
    #8
  10. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    Hi biglot please mark as Solved, see Top of Thread.
     
    MRdNk, Sep 28, 2008
    #9
  11. cwwozniak

    cwwozniak Trusted Advisor Spam Fighter

    Joined:
    Nov 28, 2005
    Messages:
    63,004
    First Name:
    Chuck
    You are very welcome. Do I get a cut of the winnings? :D

    As MRdNk mentioned, you, as the original poster, should mark this tread as being solved.
     
    cwwozniak, Sep 28, 2008
    #10
  12. biglot

    biglot Thread Starter

    Joined:
    Sep 26, 2008
    Messages:
    7
    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.
     
    biglot, Sep 29, 2008
    #11
  13. cwwozniak

    cwwozniak Trusted Advisor Spam Fighter

    Joined:
    Nov 28, 2005
    Messages:
    63,004
    First Name:
    Chuck
    I thought that the calculations for the winners and losers seemed a bit backwards. Now I understand why. :D

    I guess I'm not a punter. Never heard of Betfair until just now and Googled them..
     
    cwwozniak, Sep 29, 2008
    #12
  14. biglot

    biglot Thread Starter

    Joined:
    Sep 26, 2008
    Messages:
    7
    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!!
     
    biglot, Sep 29, 2008
    #13
  15. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    A bit more stable then the stock market at the moment too! :rolleyes:
     
    MRdNk, Sep 30, 2008
    #14

  16. Sponsor

As Seen On
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.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/753522

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Accept Learn More...
    Dismiss Notice