Formula for Accelerator

[Citrusandsage]

Hi there!
So we have a commission plan that for 0% - 100% of attainment the payout % is = the % of attainment.

For every additional point above 100% to goal the payout increases by 2%. However has to be an even point for example they employee is at 100.4% they wont get the 102%% unless they are at 101% even. I need to find a formula that does that for me. attached an image for reference (In D6)
IF(D4<=100% than multiply the % of attainment by D5 otherwise add 2% for every full point above 100% (so in this case the formula in the accelerator table for 101% would be (=D11+(0.01*2).
Where I am struggling is I do not want to base the commission calculator on the table I want the formula to do it automatically. if they get to 101% they get paid 102% if they get to 102% they get 104% and then everything in between that next full point stays at the first % so 101.98% still gets paid at 102%.

 

[Eric W]

Maybe:

Book1
	C	D
1
2	Quota	$ 1,000,000.00
3	Bookings	$ 1,010,000.00
4	% of attainment	101.00%
5	OTC	$ 100,000.00
6	Commission	$ 102,000.00
Sheet2

Cell Formulas
Range	Formula
D6	D6	=IF(D4<1,D4,1+0.02*(INT(D4*100)-100))*D5

 

[Citrusandsage]

This is amazing! Thank you so much! Question can you alter this formula to produce just the payout % opposed to the actual result? For example instead of it saying 102K for the result the answer was

102%

I just used this calc and it works perfect but I forgot I need to produce the payout % so I can write in an additional formula for retroactive pay. So I would only need the payout %., I appreciate you so much.

 

[Eric W]

Just remove the *D5 at the end:

=IF(D4<1,D4,1+0.02*(INT(D4*100)-100))

 

[Citrusandsage]

YOU JUST SAVED MY LIFE!!! Thank you so much!