Nesting IF

[gtamir]

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

[edhans · Accepted solution]

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]

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)