+ Reply to Thread
Results 1 to 3 of 3

IF formula with a set maximum and any negative numbers set to 0

  1. #1
    Registered User
    Join Date
    12-19-2011
    Location
    San Francisco
    MS-Off Ver
    Excel 2003
    Posts
    2

    IF formula with a set maximum and any negative numbers set to 0

    Brand new to the forum so i will try my best to describe the issue.

    I am creating an equation based on a sliding rate scale. I want the calculation to subtract the previous units at each rate from the total units, and if that numbre is negative to give me a result of 0 and if it is positive to give me a maximum that i establish for each tier.

    Example: Equation as it looks now (using numbers instead of cell reference)

    =IF(((150-30-50)<0),0,(150-30-50)) obviously equals 70

    The issue is that i want it to give me a response no less than 0 and no more than 50, so if 150-30-80 is above 50 it only spits out 50, and if 150-30-80 happened to be 37, it would spit out 37.

    Im not sure if i need to mix in a second =IF statement or a =Max or =Min equation.

    Thanks

  2. #2
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Excel IF equation with a set maximum and any negative numbers set to 0

    Hello horenste, Welcome to the forum.

    How about,

    =MAX(0,MIN(150-30-50,50))

    if the output is <0, this will give you 0
    if the out put is >50, this will give you 50
    Otherwise the calculated amount

    Change 15, 30, 50 to your cell reference.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  3. #3
    Registered User
    Join Date
    12-19-2011
    Location
    San Francisco
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Excel IF equation with a set maximum and any negative numbers set to 0

    That worked perfect. I was making it much to complicated. Thanks for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1