Varying number of rows


G

gary

A portion of my worksheet looks like this:

A B C D E F G H
1 13 350.40 35.04 10.00 341.64 0.00 15.00
2 13 4.00 0.40 0.00 3.90 0.00 0.00
3 13 2.82 0.28 0.00 2.75 0.00 0.00
4 13 41.64 4.16 0.00 40.60 0.00 0.00
5 13 9.22 0.92 0.00 8.99 0.00 0.00
6 13 360.44 36.04 10.00 221.67 0.00 0.00
7 13 3.74 0.36 0.00 2.30 0.00 0.00
8 13 2.82 0.28 0.00 1.73 0.00 0.00
9 13 32.28 3.22 0.00 19.85 0.00 0.00
10 13 3.60 0.36 0.00 2.21 0.00 0.00
11 536.29 13 9.22 0.92 0.00 5.67 0.00 0.00

Doing DATA/SUBTOTAL, the subtotal line looks is this:

536.29 820.18 81.98 20.00 651.31 0.00 15.00

---------------------------------------------------------

The amount in each cell gets divided by the total of ALL
cells -- which is, in this example, 1,588.47 -- which
results in the cell's percentage. Each cell's amount is
then multiplied by its percentage. Finally, each cell's
result is multiplied by the amount in the subtotal-line
(in column A).

---------------------------------------------------------

I'm having trouble with the varying number of rows for
each part-number (in column B). What formula can I use to
do the calculations? (Note: The total of all cells must
exactly equal the amount in the subtotal-line (in column
A).
 
Ad

Advertisements

G

gary

A portion of my worksheet looks like this:

A B C D E F G H
1 13 350.40 35.04 10.00 341.64 0.00 15.00
2 13 4.00 0.40 0.00 3.90 0.00 0.00
3 13 2.82 0.28 0.00 2.75 0.00 0.00
4 13 41.64 4.16 0.00 40.60 0.00 0.00
5 13 9.22 0.92 0.00 8.99 0.00 0.00
6 13 360.44 36.04 10.00 221.67 0.00 0.00
7 13 3.74 0.36 0.00 2.30 0.00 0.00
8 13 2.82 0.28 0.00 1.73 0.00 0.00
9 536.29 13 32.28 3.22 0.00 19.85 0.00 0.00
10 14 10.00 1.00 10.00 5.00 0.00 15.00
11 14 10.00 1.00 10.00 5.00 0.00 0.00
12 14 10.00 1.00 10.00 5.00 0.00 0.00
13 100.00 14 10.00 1.00 10.00 5.00 0.00 0.00

(The part-number is in column B).

====================================================

Doing DATA/SUBTOTAL, the subtotal-line for:

Part 13 is:
536.29 820.18 81.98 20.00 651.31 0.00 15.00

Part 14 is:
100.00 40.00 4.00 40.00 20.00 0.00 15.00

---------------------------------------------------------

The amount in each cell WITH THE SAME PART-NUMBER gets
divided by the Grand Total amount of that part-number's
cells -- 1,588.47 for part 13; 419.00 for part 14. This
results in a percentage for each cell.

Each cell's amount is then multiplied by its percentage.

Finally, each cell's result is multiplied by the amount in
the subtotal-line (in column A).

---------------------------------------------------------

I'm having trouble because the number of rows for the
part-numbers varies. What formula can I use for the
calculations? (Note: It would be great if the total of all
cells exactly equals the amount (in column A) in the
part-number's subtotal-line.
 

Top