Thread: Calculate Sliding Scale

Does anyone know how to make a sliding scale? It is a commission sliding scale. The scale is 60% for 1st $100, 70% for 2nd $100, 75% for 3rd $100, 20% for 4th $100, 15% over $400.

If something sold for $580:

$60 of the 1st $100

$70 of the 2nd $100

$75 of the 3rd $100

$80 of the 4th $100

$153 of the Remainder after $400 ($180)

Total=$438

So basically I need to be able to punch in the Price then it would put the commission and the remainder into the next 2 cells.

Thanks
Daniel

Daniel

Don't really follow your example as 60+70+75+80 = 285, 180*.15 = 27, so 285+27 = 312.

Set up a table in the range F3:H7 with the following values
0,0,.6
100,60,.7
200,130,.75
300,205,.4
400,285,.15

If you have your 580 in A1 then try
=VLOOKUP(A1,F3:H7,2)+((A1-VLOOKUP(A1,F3:H7,1))*VLOOKUP(A1,F3:H7,3))

rylo

Hi rylo,

Basically the example is: If something sells for say $580 that $580 would be split up into $100@40%, $100@30%, $100@25%, $100@20% and $180@15%. So if something sold for $100 even I would get $40. If something sold for $200 I would get $70 ($40+$30). If I sold something for $320 I would get ($40+$30+$25+$4).

Hope that helps. I will give you a link to my website. If you go under the 'About' section it will show you how the commission is based.

crconsignments.com

Thanks
Daniel

Hi rylo,

Basically the example is: If something sells for say $580 that $580 would be split up into $100@40%, $100@30%, $100@25%, $100@20% and $180@15%. So if something sold for $100 even I would get $40. If something sold for $200 I would get $70 ($40+$30). If I sold something for $320 I would get ($40+$30+$25+$4).

Hope that helps. I will give you a link to my website. If you go under the 'About' section it will show you how the commission is based.

crconsignments.com

Thanks
Daniel

If you would use a UDF then

Function comm(xRng As range) As Double
x = xRng.Value
If x <= 100 Then
comm = x * 0.4
Else
If x > 100 And x <= 200 Then
comm = 40 + ((x - 100) * 0.3)
Else
If x > 200 And x <= 300 Then
comm = 70 + ((x - 200) * 0.25)
Else
If x > 300 And x <= 400 Then
comm = 95 + ((x - 300) * 0.2)
Else
If x > 400 Then
comm = 115 + ((x - 400) * 0.15)
End If
End If
End If
End If
End If
End Function

Formula in workbook =comm(A2)

Hope this helps.
Anthony
“Confidence never comes from having all the answers; it comes from being open to all the questions.”
PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s) questions.”

Daniel

OK, same locations as post 2, but values are
0,0,.4
100,40,.3
200,70,.25
300,95,.2
400,115,.15

Put your value into A1 and the lookups will bring back the result.

rylo

rylo,

Works great so far! I tried a couple of numbers and they worked. Sweet!

Thank you very much

smuzoen,

I am fairly new to speadsheets. I am not sure of how to use UDF or what UDF is. Would be cool to know though.


Thanks
Daniel