If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Calculating production totals
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. . |
#3
|
|||
|
|||
Calculating production totals
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? |
#4
|
|||
|
|||
Calculating production totals
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? . |
Thread Tools | |
Display Modes | |
|
|