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]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

% Ach​

[/td][td="bgcolor:#F3F3F3"]

% Comm​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

0%​

[/td][td]

0%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

85%​

[/td][td]

50%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

100%​

[/td][td]

100%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

115%​

[/td][td]

200%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

200%​

[/td][td]

200%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td="bgcolor:#F3F3F3"]

% Ach​

[/td][td="bgcolor:#F3F3F3"]

% Comm​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

84%​

[/td][td="bgcolor:#E5E5E5"]

0%​

[/td][td]B10: =IF(A10 < $A$3, 0, PERCENTILE($B$3:$B$6, PERCENTRANK($A$3:$A$6, A10, 6)))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

85%​

[/td][td="bgcolor:#E5E5E5"]

50%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

90%​

[/td][td="bgcolor:#E5E5E5"]

67%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

95%​

[/td][td="bgcolor:#E5E5E5"]

83%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

100%​

[/td][td="bgcolor:#E5E5E5"]

100%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]

105%​

[/td][td="bgcolor:#E5E5E5"]

133%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]

110%​

[/td][td="bgcolor:#E5E5E5"]

167%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]

115%​

[/td][td="bgcolor:#E5E5E5"]

200%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]

116%​

[/td][td="bgcolor:#E5E5E5"]

200%​

[/td][td][/td][/tr]
[/table]

 

[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?