Percentage Commission on a sliding scale.

[JonPFP]

Hi, I'm looking to create a calculator in excel that will allow me to work
out the commission to be charged on an ammount of money.

lets say I have two rows... first the ammount of money, then the percentage
commission to be charged on that.
100, 200, 300, 400, 500, 1000, 1000+
3.0 , 2.5 , 2.0, 1.5, 1.0, 0.5 , 0.25
i.e. 3% charged on the first 100, 2.5% on 100-200, 0.25% on anything greater
than 1000

I want to create a calculator that will give me a result for total commision
to be charged on an ammount of money.
So if the value were 250 it would return 3% of the first hundred, 2.5% of
the second hundred and 2% of the remainder (to give 6.5)

 

[Niek Otten]

http://www.mcgimpsey.com/excel/variablerate.html

Or use this User Defined Function (UDF).
If you're new to VBA functions, read the instructions in the text at the end

--
Kind regards,

Niek Otten


' =========================================================

Function PercPerSegment(Amount As Double, Table As Range) As Double

' Niek Otten, March 31, 2006



' Progressive pricing

' First argument is the quantity to be priced

' or the amount to be taxed

' Second argument is the Price or Tax% table (vertical)

' Make sure both ends of the table are correct;

' usually you start with zero and the corresponding price or %

' Any value should be found within the limits of the table, so

' if the top slice is infinite, then use

' something like 99999999999999999 as threshold

' and =NA() as corresponding value



Dim StillLeft As Double

Dim AmountThisSlice As Double

Dim SumSoFar As Double

Dim Counter As Long



StillLeft = Amount



For Counter = 1 To Table.Rows.Count - 1

AmountThisSlice = Application.Min(StillLeft, Table(Counter + 1, 1) _

- Table(Counter, 1))

SumSoFar = SumSoFar + AmountThisSlice * Table(Counter, 2)

StillLeft = StillLeft - AmountThisSlice

Next

PercPerSegment = SumSoFar

End Function

' =========================================================



================================================

Pasting a User Defined Function (UDF)

Niek Otten, March 31, 2006



If you find a VBA function on the Internet or somebody mails you one, and you don't know how to implement it, follow these steps:



Select all the text of the function. CTRL+C (that is, press and hold down the CTRL key, press C, release both). This a shortcut
for Copy.

Go to Excel. Press ALT+F11 (same method: press and hold the ALT key, press the F11 key and release both). You are now in the
Visual Basic Editor (VBE).

From the menu bar, choose Insert>Module. There should now be a blank module sheet in front of you. Click in it and then press
CTRL+V (same method.). This a shortcut for Paste. You should now see the text of the function in the Module.

Press ALT+F11 again to return to your Excel worksheet.

You should now be able to use the function as if it were a built-in function of Excel, like =SUM(..)

================================================

 

[krismtx]

Thanks in advance for your help.

I am struggling with a sliding commission scale as well.

I have copied and pasted the UDF. How do I access it? I did not get a
opportunity to name it. I copied it into Module1.

I have copied the http://www.mcgimpsey.com/excel/variablerate.html sit
and tried to follow that step by step, and I am still confused, so
tried the user defined function.

Thanks so much!

Kri

 

[Niek Otten]

<I have copied and pasted the UDF. How do I access it? I did not get an opportunity to name it. I copied it into Module1.>

You copied it to the right place. You don't have to name it: it's named already (PercPerSegment)

An example: In your worksheet you have

A1, A2, A3 and A4 respectively:
0, 100, 500, 1000
B1, B2, B3, and B4: respectively:
0%, 2%, 4%, 5%
C1:750

Type these in a blank worksheet, so it's easier to follow

Now you want to calculate commission or tax for the 750 . This is the formula:

=percpersegment(C1,A1:B4)

It should give you 18, which is correct.

Don't hesitate to post again in this thread if you can't get it right.

 

[krismtx]

Niek,

Thank you for your patience.
I have copied exactly.
I get #NAME?

0 0 750
100 0.02
500 0.04
1000 0.05



#NAME?

What could I be still doing wrong?

Thanks,
Kris

 

[Niek Otten]

Did you copy everything, starting with the first ' ========= line up to the second one?
In the VBE (ALT-F11), choose Insert>Module and paste it there

 

[Teodomiro]

#NAME?

What could I be still doing wrong?

On the UDF, make sure you included the line

"Function PercPerSegment(Amount As Double, Table As Range) As Double"

An alternate solution to the problem is a big ole honkin' nested IF
statement:

=IF(A1>1000,(12.5+0.0025*(A1-1000)),IF(A1>500,(10+0.005*(A1-500)),IF(A1>400,(9+0.01*(A1-400)),IF(A1>300,(7.5+0.015*(A1-300)),IF(A1>200,(5.5+0.02*(A1-200)),IF(A1>100,(3+0.025*(A1-100)),(0.03*A1)))))))

Note that this formula would work for the Original Poster, but your
numbers are probably different.