Calculate Sliding Scale

[CRC]

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

[rylo]

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

[smuzoen]

You could use a UDF - place the following in a module

Function comm(xRng As range) As Double
x = xRng.Value
If x <= 100 Then
comm = x / 100 * 60
Else
If x > 100 And x <= 200 Then
comm = 60 + ((x - 100) / 100) * 70
Else
If x > 200 And x <= 300 Then
comm = 130 + ((x - 200) / 100) * 75
Else
If x > 300 And x <= 400 Then
comm = 205 + ((x - 300) / 100) * 80
Else
If x > 400 And (x - 400) > 153 Then
comm = 438
Else
If x > 400 And (x - 400) < 153 Then
comm = 285 + ((x - 400) / 100) * 153
End If
End If
End If
End If
End If
End If
End Function

The in A2 place a value e.g. 580 then in B2 place the following formula =comm(A2)
The only condition I am not clear on is what if the remainder is LESS than $153?
See attached workbook as example- Formula in B2

[CRC]

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

[CRC]

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

[smuzoen]

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.”

[rylo]

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

[CRC]

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

[CRC]

rylo,

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

Thank you very much