Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    multiple nested if statements (xp and 2003)

    I am having a problem with nested multiple if statements. I am attaching a worksheet with the item and a description which i am now accomplishing in two different columns with if statements. I placed the narrative in cell i1 of what is the goal in one formula in one cell instead of multiple cell formulas.

    Tthanks

    Martin

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: multiple nested if statements (xp and 2003)

    I think you want:
    <code>=MAX(0,F4-IF(B4>=50,20500,15500))</code>
    based on your columns rather than your description.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: multiple nested if statements (xp and 2003)

    I assume you meant "greater than or equal to 50" instead of "greater than 50", and "subtract 20,500 from col f" instead of "subtract col f from 20,500". Try this formula in H4:

    =MAX(F4-IF(B4>=50,20500,15500),0)

    and fill down.

  4. #4
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: multiple nested if statements (xp and 2003)

    Thank you. may i asked for a description of the logic of this formula.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: multiple nested if statements (xp and 2003)

    The part IF(B4>=50,20500,15500) returns 20,500 if B4 is greater than or equal than 50, otherwise 15,500. This value is subtracted from F4.
    The MAX function returns the largest of two (or more) numbers. So if the difference is positive, it will return this difference, but if the difference is negative, it'll return 0.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple nested if statements (xp and 2003)

    <code>
    =MAX(F4-IF(B4>=50,20500,15500),0)
    </code>

    The IF compares cell B4 to 50 and if B4 is greater than or equal to 50 it returns 20500, and if B4 is less than 50 it returns 15500. That result is then subtracted from cell F4. The MAX function then returns the maximum of the difference and 0, which results in zero if the result of the subtraction is negative or the difference is it is positive.
    Legare Coleman

  7. #7
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: multiple nested if statements (xp and 2003)

    Brilliant

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •