Interpolation Question

[stanleykobeblue]

I am trying to determine a bonus payout percentage using interpolation and would like some help with the formula I should use.

I have a payout percentage scale of:
Less than 1.5% improvement pays out at 0%
1.5% improvement pays out at 20%
2% improvement pays out at 100%
5% improvement pays out at 200% (capped)

Assuming that the 'percentage improvement achieved' is 1.87%, I need to determine the payout percentage using interpolation.

Thanks in advance for any ideas!

 

[sijpie]

1.85 falls between the 1.5 and 2% improvement

The difference between 1.5 and 2.0 is 0.5 % this leads to a bonus change of 20% top 100% = 80%

so per 0.01% change (50 steps from 1.50 to 2.00) we have 80% bonus to split up is 80/50 = 1.6 % bonus

so bonus at 1.87 = 20% + 37 * 1.6% = 79.2% bonus

Assuming improvemnt % is in cell A1 then
=if(A1<1.5,0,if(A1<2,20+(2-A1)*1.6*100,if(A1<5,100+(5-A1)*100/3,200)))

 

[btadams]

Never Mind

 

[stanleykobeblue]

Thank you for your help...