Target based acheivement

[vijaymehta]

Hi all, I am facing some issue while setting up formula for target based incentive. Problem set has 3 classes:

Below 80% achievement - payout is 0%
at 92.5% achievement - payout is 100%
Between 80% to 92.5% - payout is proportion (basically the gap between 80% and 92.5% represents 100%)
At 100%, payout is 107.5%
between 92.5% and 100% - payout is proportion (represents 7.5% basically)

I am struggling to get the proportion right by providing input cell to set this class limits. Tried using with vlookup (with false argument) but it misses on proportion calculation. Using If formula, I am messing it up on proportion output.

No VBA, it needs to be formula driven with provision to provide input criteria.

Any help will be much appreciated.

 

[oldbrewer]

25	0
79	0
80	0
81	8
91	88
92.5	100
93	100.5
97	104.5
100	107.5
		are these the answers you expect ?

<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[vijaymehta]

Yes, your understanding is right.

 

[oldbrewer]

25	0
79	0
80	0
81	8
91	88
92.5	100
93	100.5
97	104.5
100	107.5
		are these the answers you expect ?
		formula in B1
		=IF(A1<80,0,IF(A1<92.5,((A1-80)/12.5)*100,(((A1-92.5)/7.5)*7.5)+100))

<colgroup><col width="64" span="12" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[shg]

Another way:

	A​	B​	C​	D​	E​	F​
1​	Attainment​	Incentive​		Attainment​	Incentive​
2​	80.0%​	0.0%​		80%​	0.0%​	E2: =PERCENTILE($B$2:$B$4, PERCENTRANK($A$2:$A$4, D2, 6))
3​	92.5%​	100.0%​		81%​	8.0%​
4​	100.0%​	107.5%​		82%​	16.0%​
5​				83%​	24.0%​
6​				84%​	32.0%​
7​				85%​	40.0%​
8​				86%​	48.0%​
9​				87%​	56.0%​
10​				88%​	64.0%​
11​				89%​	72.0%​
12​				90%​	80.0%​
13​				91%​	88.0%​
14​				92%​	96.0%​
15​				93%​	100.5%​
16​				94%​	101.5%​
17​				95%​	102.5%​
18​				96%​	103.5%​
19​				97%​	104.5%​
20​				98%​	105.5%​
21​				99%​	106.5%​
22​				100%​	107.5%​

 

[vijaymehta]

Yes, this helps. Thanks

 

[vijaymehta]

Thank you for showing percentile usage. I will go for this.. thank you so much.

 

[shg]

You're welcome.