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!

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

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






wrote in message oups.com...
| 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!
|

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.

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.

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

--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


wrote in message
ups.com...
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.

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

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.

In article .com,
wrote:

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!

Correction: The -0.01 should have been -0.04

In article ,
JE McGimpsey wrote:

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 wrote:
Correction: The -0.01 should have been -0.04

In article ,
JE McGimpsey wrote:

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 - That is an amazing formula and works great! Thank you!!!