Sliding Scale Formula (% payout)

[mgeiss]

Hello,

I could use some help with a sliding scale formula. I need a formula that does the following:

if a sales rep achieves 85% of sales, they will achieve 50% of their bonus. If a sales rep achieves 100% of sales, they achieve 100% of their bonus. If they achieve 115% of sales, they’ll get 200% of their bonus. I created an if statement that looks at the % achieved and returns the payout %, but I want this to be on a scale rather than a fixed % payout. So if the sales rep achieves 98% of sales, I want the prorated payout %. Not 50%, like my formula is giving me.

% Achieved Payout
85% 50%
100% 100%
115% 200%

This is the formula I made, but it doesn’t do it on a scale like I would like to do it. Any suggestions?

=IF(C$10>=Summary!$H$8,($C$5*$B$7)*Summary!$I$8,IF(C$10>=Summary!$H$6,($C$5*$B$7)*Summary!$I$6,IF(AND(C$10<=Summary!$H$5,C$10>=Summary!$H$4),($C$5*$B$7)*Summary!$I$5,0)))

 

[shg]

What if they do 50% of target? 150% of target?

 

[mgeiss]

if they get 50% of target they get zero. They only begin to receive payout if >=85% is achieved at which point they get 50%. if they achieve >=115% they achieve 200%.

 

[shg]

	A​	B​	C​
1​	% Ach​	% Comm​	​
2​	0%​	0%​
3​	85%​	50%​
4​	100%​	100%​
5​	115%​	200%​
6​	200%​	200%​
7​
8​
9​	% Ach​	% Comm​
10​	84%​	0%​	B10: =IF(A10 < $A$3, 0, PERCENTILE($B$3:$B$6, PERCENTRANK($A$3:$A$6, A10, 6)))
11​	85%​	50%​
12​	90%​	67%​
13​	95%​	83%​
14​	100%​	100%​
15​	105%​	133%​
16​	110%​	167%​
17​	115%​	200%​
18​	116%​	200%​

 

[mgeiss]

I cannot thank you enough! This worked perfectly!

 

[mgeiss]

While this worked perfectly, I'm curious. What does the 6 mean in the formula?

 

[shg]

Did you look at Help for the PERCENTRANK function?