# NEED FORMULA HELP

B

#### 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

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