Designation & % of Achievement

[muralli]

Hi friends,

I have a Sales Incentive Program that payouts to staff based on their designation and % achieved. Below is the criteria table.


[TABLE="width: 432"]
<tbody>[TR]
[TD]Designation[/TD]
[TD]Pay-1
90%[/TD]
[TD]Pay-2
100%[/TD]
[TD]Pay-3
110%[/TD]
[TD]Pay-4
120%[/TD]
[/TR]
[TR]
[TD]Sales Associate[/TD]
[TD]60[/TD]
[TD]100[/TD]
[TD]150[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]Watches Technician[/TD]
[TD]60[/TD]
[TD]100[/TD]
[TD]150[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]Senior Sales Associate[/TD]
[TD]60[/TD]
[TD]100[/TD]
[TD]150[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]Visual Merchandiser[/TD]
[TD]75[/TD]
[TD]150[/TD]
[TD]225[/TD]
[TD]450[/TD]
[/TR]
[TR]
[TD]Assistant Shop Manager[/TD]
[TD]75[/TD]
[TD]150[/TD]
[TD]225[/TD]
[TD]450[/TD]
[/TR]
[TR]
[TD]Shop Manager[/TD]
[TD]75[/TD]
[TD]175[/TD]
[TD]265[/TD]
[TD]525[/TD]
[/TR]
</tbody>[/TABLE]

Scenario-1: Shop target is 10,000; sales achieved is 9,500; achievement is 95%; hence payout will be based on 'Pay-1'
Scenario-2: Shop target is 10,000; sales achieved is 11,500; achievement is 115%; hence payout will be based on 'Pay-3'
Scenario-3: Shop target is 10,000; sales achieved is 14,000; achievement is 140%; hence payout will be based on 'Pay-4'

If the 90% achievement threshold is not achieved, no incentive will be paid out. No rounding off % achieved is allowed. Policy is strict for payout.

How do I write a formula or array formula for this situation?

Please help.

Thank you.

Regards
Muralli Srinivasan

 

[AlanY]

one way

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J
1	Designation	Pay-1	Pay-2	Pay-3	Pay-4		target	achieved
2		90%	100%	110%	120%
3	Sales Associate	60	100	150	300		10000	9500	95%	Pay-1
4	Watches Technician	60	100	150	300		10000	11500	115%	Pay-3
5	Senior Sales Associate	60	100	150	300		10000	14000	140%	Pay-4
6	Visual Merchandiser	75	150	225	450
7	Assistant Shop Manager	75	150	225	450
8	Shop Manager	75	175	265	525
Sheet3

Cell Formulas
Range	Formula
J3	=LOOKUP(I3,$B$2:$E$2,$B$1:$E$1)

 

[muralli]

Thank you Alan. Though I was looking for the full solution (payout amount), you gave me the right lead. I have solved the issue.

I used a combination of Index-Match-Lookup formula for the final formula and it worked.

Amazing platform this one...

one way

Excel 2013/2016

	A	B	C	D	E	F	G	H	I	J
Designation	Pay-1	Pay-2	Pay-3	Pay-4	target	achieved
Sales Associate
Watches Technician
Senior Sales Associate
Visual Merchandiser
Assistant Shop Manager
Shop Manager

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]90%[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]110%[/TD]
[TD="align: right"]120%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]60[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]9500[/TD]
[TD="align: right"]95%[/TD]
[TD="bgcolor: #E2EFDA"]Pay-1[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]60[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]11500[/TD]
[TD="align: right"]115%[/TD]
[TD="bgcolor: #E2EFDA"]Pay-3[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]60[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]14000[/TD]
[TD="align: right"]140%[/TD]
[TD="bgcolor: #E2EFDA"]Pay-4[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]75[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]225[/TD]
[TD="align: right"]450[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]75[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]225[/TD]
[TD="align: right"]450[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]75[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]265[/TD]
[TD="align: right"]525[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]J3[/TH]
[TD="align: left"]=LOOKUP(I3,$B$2:$E$2,$B$1:$E$1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[AlanY]

you're welcome