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)

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 InsertModule. 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(..)

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






"JonPFP" wrote in message ...
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)

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 an
opportunity to name it. I copied it into Module1.

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

Thanks so much!

Kris


--
krismtx
------------------------------------------------------------------------
krismtx's Profile: http://www.excelforum.com/member.php...o&userid=33462
View this thread: http://www.excelforum.com/showthread...hreadid=531821

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


--
Kind regards,

Niek Otten

"krismtx" wrote in message
...

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 an
opportunity to name it. I copied it into Module1.

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

Thanks so much!

Kris


--
krismtx
------------------------------------------------------------------------
krismtx's Profile: http://www.excelforum.com/member.php...o&userid=33462
View this thread: http://www.excelforum.com/showthread...hreadid=531821

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


--
krismtx
------------------------------------------------------------------------
krismtx's Profile: http://www.excelforum.com/member.php...o&userid=33462
View this thread: http://www.excelforum.com/showthread...hreadid=531821

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

"krismtx" wrote in message
...

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


--
krismtx
------------------------------------------------------------------------
krismtx's Profile: http://www.excelforum.com/member.php...o&userid=33462
View this thread: http://www.excelforum.com/showthread...hreadid=531821

krismtx Wrote:
#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(A11000,(12.5+0.0025*(A1-1000)),IF(A1500,(10+0.005*(A1-500)),IF(A1400,(9+0.01*(A1-400)),IF(A1300,(7.5+0.015*(A1-300)),IF(A1200,(5.5+0.02*(A1-200)),IF(A1100,(3+0.025*(A1-100)),(0.03*A1)))))))

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


--
Teodomiro


------------------------------------------------------------------------
Teodomiro's Profile: http://www.excelforum.com/member.php...o&userid=33140
View this thread: http://www.excelforum.com/showthread...hreadid=531821