Forum Discussion

gtamir's avatar
gtamir
Post Patron
8 years ago
Solved

Nesting IF

I have this formula to give a bonus from 5 to 120 acording to the value in Total. I want to add a condition that if TOTAL is geater then 200, then 7.5%. Bonus= ROUNDUP(IF(sum(Sales[Total])<=100,5,MI...
Need Help
  • edhans's avatar
    edhans
    8 years ago

    See if this helps. The formula below does this logic:

    • If sales < 1,000, then 5% bonus
    • If sales <> 1000 and 2000 then 6% bonus
    • otherwise (i.e. > 2000) then 7.5% bonus

     

    Bonus = 
ROUND(
    IF(
        [Total Sales] < 1000,
        [Total Sales] * .05,
        IF(
            [Total Sales]>= 1000 && [Total Sales] < 2000,
            [Total Sales] * .06,
            [Total Sales] * .075
        )
    )
    ,2
)
edhans's avatar
edhans
Community Champion
8 years ago

See if this helps. The formula below does this logic:

  • If sales < 1,000, then 5% bonus
  • If sales <> 1000 and 2000 then 6% bonus
  • otherwise (i.e. > 2000) then 7.5% bonus

 

Bonus = 
ROUND(
    IF(
        [Total Sales] < 1000,
        [Total Sales] * .05,
        IF(
            [Total Sales]>= 1000 && [Total Sales] < 2000,
            [Total Sales] * .06,
            [Total Sales] * .075
        )
    )
    ,2
)
  • gtamir's avatar
    gtamir
    Post Patron
    8 years ago

    Yes, I adopted it to my needs and it works. 

     

    Bonus 1303 =
    ROUNDUP(
    IF(
    [Sum Total] <=100, 5,
    IF ([Sum Total]> 100 && [Sum Total] <= 200,
    [Sum Total] * .05,MIN(120,[Sum Total]*.075) ) ) ,0)