Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NEED FORMULA HELP
Below is delemia. I work for a doctors office as a subcontractor- I collect
money from patients. Below is a portion of our spread sheet to be paid. Right now I have it set up as c44 is my running total collected which veries month to month. I manually have to place (c44 total) amount into c46 through c49, then I have D46 - d49 figure out the information I manually placed. figuring out the individual percentages. What I would like it to do is: - have the c44 distribute the money into the spots c46 - c49 and if possible have the percentages figured out at the same time. If not that is ok. I am not sure how to write the formula. What I need the formula to do is A B C D 44 TOTAL REPORT AMT. $8000.00 45 46 BREAKOUT 5000 35% $5000.00 $1750.0 47 $5001 - $7500 30% $2500.00 $750.00 48 $7501 - $10000 28% $ 500.00 $140.00 49 $10000 35% $0.00 50 CONFIRM TOTAL AMT $8000.00 COMMISSION DUE $2640.00 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NEED FORMULA HELP
On Thu, 28 Jan 2010 14:18:01 -0800, brushtec
wrote: Below is delemia. I work for a doctors office as a subcontractor- I collect money from patients. Below is a portion of our spread sheet to be paid. Right now I have it set up as c44 is my running total collected which veries month to month. I manually have to place (c44 total) amount into c46 through c49, then I have D46 - d49 figure out the information I manually placed. figuring out the individual percentages. What I would like it to do is: - have the c44 distribute the money into the spots c46 - c49 and if possible have the percentages figured out at the same time. If not that is ok. I am not sure how to write the formula. What I need the formula to do is A B C D 44 TOTAL REPORT AMT. $8000.00 45 46 BREAKOUT 5000 35% $5000.00 $1750.0 47 $5001 - $7500 30% $2500.00 $750.00 48 $7501 - $10000 28% $ 500.00 $140.00 49 $10000 35% $0.00 50 CONFIRM TOTAL AMT $8000.00 COMMISSION DUE $2640.00 Assuming that you have no data in cells A45 and cell C45, try this: A46: 5000 A47: 7500 A48: 10000 A49: 99999 B46: 35% B47: 30% B48: 28% B49: 35% This table can be extened with more limits and percentages if needed. The formulas in column C can handle that. C44 (i.e. your input cell): 8000 C46: =MIN(C$44-SUM(C$45:C45),A46-A45) D46: =C46*B46 Copy cells C46 and D46 down as far as the table in columns A and B, in your example down to row 49 In the cell where you want the CONFIRM TOTAL AMT: =SUM(C46:C49) (change C49 if you have a larger table) In your example this will show the value 8000. In the cell where you want the COMMISION DUE: =SUM(D46:D49) (change D49 if you have a larger table) In your example this will show the value 2640. Hope this helps / Lars-Åke |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|