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,MIN(120,0.05*SUM(Sales[Total]))),0)

 

Thanks Giora

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
)

2 Replies

  • 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)