NEED FORMULA HELP

[brushtec]

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

 

[Lars-Åke Aspelin]

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(D4649)
(change D49 if you have a larger table)
In your example this will show the value 2640.

Hope this helps / Lars-Åke