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

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






"JonPFP" <JonPFP@discussions.microsoft.com> wrote in message news:B39F4D53-D99F-4D53-8B81-1CE354F2388C@microsoft.com...
> 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)

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

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


--
Kind regards,

Niek Otten

"krismtx" <krismtx.267lky_1144942644.6151@excelforum-nospam.com> wrote in message
news:krismtx.267lky_1144942644.6151@excelforum-nospam.com...
>
> 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
>

[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

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

[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

"krismtx" <krismtx.267o80_1144945809.0873@excelforum-nospam.com> wrote in message
news:krismtx.267o80_1144945809.0873@excelforum-nospam.com...
>
> 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
>