Calculating production totals

I am working on a database where I need to take a monthly
production figure add it to the running total for the
year and multiply everything below $67,800 by 50% and
everything above $67,800 by 95%. It would also really be
great if I could somehow reset the annual figure every
year in September. Column A is a date figure so I am
thinking that somehow I can tie it to that. Can anyone
help me think of an easy way to put this formula
together. I would greatly appreciate the help.

Hi Cameron,

Which figure do you want to calculate, the monthly or
yearly? How is your data set up? Can you provide a small
example?

Biff

-----Original Message-----
I am working on a database where I need to take a monthly
production figure add it to the running total for the
year and multiply everything below $67,800 by 50% and
everything above $67,800 by 95%. It would also really be
great if I could somehow reset the annual figure every
year in September. Column A is a date figure so I am
thinking that somehow I can tie it to that. Can anyone
help me think of an easy way to put this formula
together. I would greatly appreciate the help.



.

Biff,

Thank you for your reply. My sheet is set up this way

Cell A1 = is an average sale price
Cell A2 = is an average commission percentage

Further down the sheet I have a table set up.

Column A = Date (the sheet is set up on a monthly basis)

Column B = number of units sold

Column C = Column B x $A$1 x $A$2 (calculates gross
commission earned for that month)

Column D = Current column C + previous month column D
(keeps a running total of the gross commission earned for
the year)

Column E = I would like this column to calculate the net
commission earned for the month (this is the column that
I am having trouble with writing a formula for)

The commission structure operates like this. For every
gross dollar (column B and C) earned under $92,500.00,
the company takes 50%. For every gross dollar earned
over $92,500.00 the company takes 5%. The companies
fiscal year ends in September, so I need to reset the
annual total. I have had decent luck accomplishing this
with a conditional formula =IF(month(A109,C10+D9,C10).
Can you help me develop a function to calculate the net
commission earned given these criteria?

Hi Cameron,

Let me make sure I understand the commission calculation:

It's 50% for the first 92500 *PLUS* 5% for the balance
over 92500???

If that's the case try this formula, adjust the cell ref
as needed:

=IF(C10=92500,C10*0.5,92500*0.5+(C10-92500)*0.05)

If this is not what you're looking for, you can send me
the file if possible so I can better see what it is you
need. You would need to change the address to biff_in_pitt.

Biff

-----Original Message-----
Biff,

Thank you for your reply. My sheet is set up this way

Cell A1 = is an average sale price
Cell A2 = is an average commission percentage

Further down the sheet I have a table set up.

Column A = Date (the sheet is set up on a monthly basis)

Column B = number of units sold

Column C = Column B x $A$1 x $A$2 (calculates gross
commission earned for that month)

Column D = Current column C + previous month column D
(keeps a running total of the gross commission earned for
the year)

Column E = I would like this column to calculate the net
commission earned for the month (this is the column that
I am having trouble with writing a formula for)

The commission structure operates like this. For every
gross dollar (column B and C) earned under $92,500.00,
the company takes 50%. For every gross dollar earned
over $92,500.00 the company takes 5%. The companies
fiscal year ends in September, so I need to reset the
annual total. I have had decent luck accomplishing this
with a conditional formula =IF(month(A109,C10+D9,C10).
Can you help me develop a function to calculate the net
commission earned given these criteria?

.