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

 

[Sumit_123]

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)

Works great but in scenarios where calculation is 103.9%. It's showing 89% instead of 81(results are coming the other way)

 

[kvsrinivasamurthy]

This formula covers entire range.

=IFERROR(VLOOKUP(G2,$R$6:$S$30,2,1)+IF(G2<98%,MOD(G2,0.01)*10,IF(G2<102%,0,-MOD(G2,0.01)*10)),0)