Creating a commission function

[Bob]

I'm trying to create a commission function. The commission schedule is
15% of the first 15,000, plus 10% of 15,001 - 25,000, plus 5% of
25,001-50,000, plus 1% 50,001 and over.

I would like this to work as a function in Excel so that we could
enter: =C(any number) and it would calculate the above commission
schedule.

I'm having trouble getting an IF statement to work which I was hoping
to nest for each layer of the above schedule.

Thanks in advance for any advice!

 

[Niek Otten]

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

Or you could use the UDF below. If you don't know VBA (yet), follow the instructions 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(..)

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






| I'm trying to create a commission function. The commission schedule is
| 15% of the first 15,000, plus 10% of 15,001 - 25,000, plus 5% of
| 25,001-50,000, plus 1% 50,001 and over.
|
| I would like this to work as a function in Excel so that we could
| enter: =C(any number) and it would calculate the above commission
| schedule.
|
| I'm having trouble getting an IF statement to work which I was hoping
| to nest for each layer of the above schedule.
|
| Thanks in advance for any advice!
|

 

[Bob]

Yes, I'm trying to do this in VBA with nested if statements and naming
it as a function so I can use it on a sheet to calculate the
commissions.

I'm not sure how a table would work with this and with only 4 layers it
should be easy with if statements.

 

[Bob]

Yes, I'm trying to do this in VBA with nested if statements and naming
it as a function so I can use it on a sheet to calculate the
commissions.

I'm not sure how a table would work with this and with only 4 layers it
should be easy with if statements.

 

[Dana DeLouis]

Hi. Would this work for you instead:

Function Commission(n)
Commission = WorksheetFunction.Min(0.15 * n, 750 + 0.1 * n, 2000 + 0.05 *
n, 4000 + 0.01 * n)
End Function

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(A1>={0,15000,25000,50000},A1-{0,15000,25000,50000},
{0.15,-0.05,-0.05,-0.01})

See

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

for an explanation and a more flexible table-based method.

 

[JE McGimpsey]

Correction: The -0.01 should have been -0.04

 

[Bob]

Correction: The -0.01 should have been -0.04

JE - That is an amazing formula and works great! Thank you!!!

 

[Bob]

Thank you, that is exactly what I was looking for - now I have two
solutions - a VBA one and a formula one!