Need help with sliding scale formula

[earlers]

I have a pivot table and I want to put in a formula to calculate a commission% based off of the margin%. I'm having a hard time coming up with a simple formula.

Below is the structure. Basically, if a salesperson sells an item with X margin% then he/she gets paid x commission%.

40%-49% = range of 2.6% to 4.9%
50% - 59% = range of 5% to 9.9%
60% and above = 10%
Below 40% = 2.5%

Any help would be greatly appreciated!!

 

[mole999]

just as a thought, would you not be more financially efficient if you did this as banded values, in 5% increments.

The downside of sliding scale, will be the minute financial changes in payroll.

Your items then attract different bands dependant on volume sold, which obviously will have a mark down for volume

sell 100 items of this type banned as, 200 would be different etc, the incentive to sell more

 

[shg]

That's a very odd scale.

       --A-- --B---
   1    Inp   Out  
   2    0.0%  2.50%
   3   39.0%  2.50%
   4               
   5   39.5%  2.50%
   6   40.0%  2.60%
   7   41.0%  2.86%
   8   42.0%  3.11%
   9   43.0%  3.37%
  10   44.0%  3.62%
  11   45.0%  3.88%
  12   46.0%  4.13%
  13   47.0%  4.39%
  14   48.0%  4.64%
  15   49.0%  4.90%
  16   49.5%  4.90%
  17               
  18   50.0%  5.00%
  19   51.0%  5.54%
  20   52.0%  6.09%
  21   53.0%  6.63%
  22   54.0%  7.18%
  23   55.0%  7.72%
  24   56.0%  8.27%
  25   57.0%  8.81%
  26   58.0%  9.36%
  27   59.0%  9.90%
  28   59.5%  9.90%
  29               
  30   60.0% 10.00%

The formula in B2 and copied down is

=CHOOSE(MATCH(A2, {0,40,49,50,59,60}%),
2.5%,
2.6% + (4.9%-2.6%)*(A2-40%)/(49%-40%),
4.9%,
5% + (9.9%-5%)*(A2-50%)/(59%-50%),
9.9%,
10%)

 

[mole999]

nice to see the odd can be calculated

 

[earlers]

Sgh4421,

Thanks for your solution but I only have one problem. I can't use a reference formula in my pivot table. However, I think that you still may be able to assist me with the formula. Currently, I'm using the below formula but it appears that the numbers are slightly off.


=IF('Margin%'>0.6,'Net Revenue'*0.1,IF('Margin%'>0.4,'Net Revenue'*('Margin%'-0.4)/2,'Net Revenue'*0.025))

I think the problem may lie in the below portion.

('Margin%'-0.4)/2

 

[shg]

I don't see how that formula relates to your prior question or what it's supposed to do.

 

[earlers]

I don't see how that formula relates to your prior question or what it's supposed to do.

It is basically the same.

If margin is greater than 60% pay 10%= ('Margin%'>0.6,'Net Revenue'*0.1

If margin is greater than 40% pay range (2.5% - 9.99%) = Margin%'>0.4,'Net Revenue'*('Margin%'-0.4)/2

Let me know if I'm not explaining it correctly.

 

[shg]

       --A--- --B--- ---C----
   1             Net  $1,000 
   2                         
   3   Margin Comm%   Comm$  
   4     0.0%  2.50%  $25    
   5    40.0%  2.50%  $25    
   6    41.0%  2.88%  $29    
   7    42.0%  3.25%  $33    
   8    43.0%  3.63%  $36    
   9    44.0%  4.00%  $40    
  10    45.0%  4.38%  $44    
  11    46.0%  4.75%  $48    
  12    47.0%  5.13%  $51    
  13    48.0%  5.50%  $55    
  14    49.0%  5.88%  $59    
  15    50.0%  6.25%  $63    
  16    51.0%  6.63%  $66    
  17    52.0%  7.00%  $70    
  18    53.0%  7.38%  $74    
  19    54.0%  7.75%  $78    
  20    55.0%  8.13%  $81    
  21    56.0%  8.50%  $85    
  22    57.0%  8.88%  $89    
  23    58.0%  9.25%  $93    
  24    59.0%  9.63%  $96    
  25    60.0% 10.00%  $100

The formula in B4 and down is

=CHOOSE(MATCH(A4, {0,40,60}%), 2.5%, 2.5% + (10%-2.5%)*(A4-40%)/(60%-40%), 10%)

 

[earlers]

       --A--- --B--- ---C----
   1             Net  $1,000 
   2                         
   3   Margin Comm%   Comm$  
   4     0.0%  2.50%  $25    
   5    40.0%  2.50%  $25    
   6    41.0%  2.88%  $29    
   7    42.0%  3.25%  $33    
   8    43.0%  3.63%  $36    
   9    44.0%  4.00%  $40    
  10    45.0%  4.38%  $44    
  11    46.0%  4.75%  $48    
  12    47.0%  5.13%  $51    
  13    48.0%  5.50%  $55    
  14    49.0%  5.88%  $59    
  15    50.0%  6.25%  $63    
  16    51.0%  6.63%  $66    
  17    52.0%  7.00%  $70    
  18    53.0%  7.38%  $74    
  19    54.0%  7.75%  $78    
  20    55.0%  8.13%  $81    
  21    56.0%  8.50%  $85    
  22    57.0%  8.88%  $89    
  23    58.0%  9.25%  $93    
  24    59.0%  9.63%  $96    
  25    60.0% 10.00%  $100

The formula in B4 and down is

=CHOOSE(MATCH(A4, {0,40,60}%), 2.5%, 2.5% + (10%-2.5%)*(A4-40%)/(60%-40%), 10%)

Excellent! Just what I needed.

 

[pgc01]

Hi

Try also a variation on shg's formula:

=MAX(2.5%,MIN(10%,2.5%+(10%-2.5%)*(A4-40%)/(60%-40%)))