Calculation formula for incentive

[Ecel Dumbo]

Hello I need a formula to calculate Incentives based on the below table


For example- IF % of Target achieved is 95.63%, incentive will be 60% of Sales ($1667) , answer will be $1000


Sales is $1667.


% of Target Incentive- % of Sales

85-90.99 20%

91-92.99 40%

93-94.99 50%

95-96.99 60%

97-98.99 80%

99-100.99 100%

101.103.99 110%

104-105.99 120%

Additional%up to +1%


Thanks

 

[Hui]

Excel Dumbo


I would rearrange your table as shown below

being A1:B10

[pre]

% of Target	Incentive %
85	20%
91	40%
93	50%
95	60%
97	80%
99	100%
101	110%
104	120%
200	121%[/pre]

Then use a formula

=Sales*VLOOKUP(%_of_Target,A2:B10,2)