Calculate incentives based on percentage achievement

[Sumit_123]

Hi All,
I have a specific problem to which I am not able to find a solution for, any help will be greatly appreciated.
C column is the actuals, F is the target and G is the result of achievement C/F.
Now the incentive band for anyone is if any employee achieves between 98-102% they get full commision.
Anything above or below reduces the commission as shown below in range(R-S).
I calculated the outcome column using the vlookup to bring the incentive to closest percentage. But what I am intending to achieve is such as in Row 3 the achievement is 105.5%. Based on the range table it should be 70% as the outcome column is showing but to be precise the result should be 65% and not 70% because the achievement was .50% above 105% and as the achievement % goes above the incentive decreases.

Please help out and if you have any questions or doubts to clarify please reach

 

[bebo021999]

Try to add up G3 to 0.5% then VLOOKUP this new value
=VLLOKUP(G3+0.005,...

 

[Sumit_123]

Try to add up G3 to 0.5% then VLOOKUP this new value
=VLLOKUP(G3+0.005,...

Thanks for your reply.
But how will that make the calculation dynamic?

 

[bebo021999]

Thanks for your reply.
But how will that make the calculation dynamic?

After comming back, I discover that is not simple like I though before...
Try to attach a XL2BB mini sheet

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

or sample file (with gg drive, dropbox...)

 

[kvsrinivasamurthy]

Try

=IFERROR(VLOOKUP(G2,$R$6:$S$30,2,1)-MOD(G2,0.01)*10,0)

 

[Sumit_123]

Try

=IFERROR(VLOOKUP(G2,$R$6:$S$30,2,1)-MOD(G2,0.01)*10,0)

Works great. See as below. Only thing is for some of the results they were coming in negative. In reality any employee out of the achievement band range shouldnt be getting any incentive, I can filter out all the negative ones as they anyways they dont qualify but are we able to cover for that in the formula.
Really appreciate your support, thanks for your help.

 

[Sumit_123]

After comming back, I discover that is not simple like I though before...
Try to attach a XL2BB mini sheet

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

or sample file (with gg drive, dropbox...)

Thankyou. Another member replied on the post and was able to get the results else I would have shared the file.
If you still want, I can upload?

 

[Sumit_123]

Works great. See as below. Only thing is for some of the results they were coming in negative. In reality any employee out of the achievement band range shouldnt be getting any incentive, I can filter out all the negative ones as they anyways they dont qualify but are we able to cover for that in the formula.
Really appreciate your support, thanks for your help.






View attachment 82573

I created another column with IF condition IF(Outcome1>0,"Outcome1",0) and achieved what I was looking for. Though there could be a better way to achieve this but its working for now.
Thanks for your help again

 

[Sumit_123]

I think I got too excited, But I had to use below because incentives were 100% between 98 and 102 and if the value was above 102% based on the formula provided it was subtracting the % which was correct but if the achievement was below 98 then results were getting subtracted as well which wasn't right(had to add the MOD statement). In the O column I just simply put an IF statement that only show +ve results and ignore -ve
If there is a simple way to achieve this, i am all ears.

Regards,

 

[bebo021999]

Other approach:

Book2
	G	H	I									R	S
1	Calc	Outcome
2	100.00%	100%
3	117.20%	0%
4	105.50%	65%
5	82.00%	0%										Achievement	Incentive
6	87.20%	0%										88%	0%
7	79.10%	0%										89%	10%
8	95.40%	74%										90%	20%
9												91%	30%
10												92%	40%
11												93%	50%
12												94%	60%
13												95%	70%
14												96%	80%
15												97%	90%
16												98%	100%
17												99%	100%
18												100%	100%
19												101%	100%
20												102%	100%
21												103%	90.0%
22												104%	80.0%
23												105%	70.0%
24												106%	60.0%
25												107%	50.0%
26												108%	40.0%
27												109%	30.0%
28												110%	20.0%
29												111%	10.0%
30												112%	0.0%
Sheet2

Cell Formulas
Range	Formula
G2:G3	G2	=C2/F2
H2:H8	H2	=IF(OR(G2>=1.12,G2<=0.88),0,INDEX(IF(G2>102%,$S$6:$S$31,$S$5:$S$30),MATCH(G2,$R$5:$R$30,1))+MOD(G2*100,1)/10)