Nested IF statements

[dafyddhollis]

Hello, I'm trying to build a dynamic spreadsheet to calculate the bonus a partner will earn for a specific target achievement level.

The way the bonuse programme works is as follows:

The partner earns 0 until they hit 80% achievement.

At 80% they earn a rebate of 0.6%

Between 80 and 100% achivement they earn 0.6% rebate on the business between 0% and 80% plus 3.6% on the business over 80% (i.e. if they get to 85% they earn 0.6% on the business between 0% and 80% and then 3.6% on the 5%)

When they hit 100% they get a flat rate 1.2% bonus up to 200% where they cap out.

Any help would be great!

 

[dafyddhollis]

This is the formula I was trying to use:

=if(B22>80%,0,if(B22=80%,B21*.006),if(B22<100%,(B20*.8 *.006 +(B21- B20*.8)*.036),if(B22>=100%,B21*.012,if(B22>200%,B20*2 *1.2))))

Where B22 was the % achievement against target
B20 is the target
B22 is the actual

 

[cstimart]

Something like this?

=IF(B22<0.8,0,IF(B22<0.85,(B20*0.8*0.006)+((B21-(B20*0.8))*0.036),IF(B22<2,B21*0.012,0)))

 

[cstimart]

Something like this?

=IF(B22<0.8,0,IF(B22<0.85,(B20*0.8*0.006)+((B21-(B20*0.8))*0.036),IF(B22<2,B21*0.012,0)))

In thinking...this might be more accurate

=IF(B22<=0.8,0,IF(B22<1,(B20*0.8*0.006)+((B21-(B20*0.8))*0.036),B21*0.012))