template or formula for piece commission calculation

[LMM300M]

I am looking for a template or calcuation for piece work.
i.e. 1-100 gets paid at .10, 101-200 gets paid at .20 etc.
so a person selling 150 would get paid
100 * .10 for 10
50 * .20 for 10
for a total of $20.00
drawing a formula blank at the moment.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...43c&dg=microsoft.public.office.templates.misc

 

[JoAnn Paules]

Sometimes templates for your exact needs just aren't available. But when you
create one that does meet your needs, you can always submit it to Microsoft
to share it with others.

--

JoAnn Paules
Microsoft MVP - Publisher

How to ask a question
http://support.microsoft.com/kb/555375

 

[Helpful One]

Here is how I would set up my calculation in excel which
you can modify as you need to customize to your real
situation. I'll use specific cells but you can adjust them
to anywhere you want.

First, enter the number of units produced or sold in Cell
C2.

In cell B4, enter 100
In cell B5, enter 200
In cell B6, enter 300
etc. for as far as you want to go.

In cell C4, enter the formula =IF(C2>B4,100,C2)
In cell C5, enter the formula =
=IF(C$2>B4,IF(C$2>B5,100,C$2-B4),0)
Then copy the formula from C5 to the remaining cells C6:C_
to as far as you want to go.

In D4, enter the unit piece price for the first 100 units
In D5, enter the unit piece price for the next 100 units
etc. for as far as you want to go.

In E4, enter the formula =C4*D4
Then copy the formula from E4 into the remaing cells E5:E_
to as far as you want to go.

In the first row beyond your last entry in column B enter
"Total"
In this same row in column E, enter the formula
=SUM(E4:E___) with the ___ being the row number just above
the row in which this formula is placed.

Finally, to warn you of problems, insert the following
formula in the next row below your last.
=IF(C2>B___,"Number larger than template calculates","OK")
with the ___ being the largest number in column B. You can
use the FORMAT,Conditional Formating menu to Set the color
of the Font to Green if the value of this cell is equal to
"OK", or Red if the value of this cell not equal to "OK".

Hope this helps





message
news:[email protected]...