B
bart hudkins
Does anyone know how to make a formula “reflective” of another formula?
I frequently need to calculate assembly costs at different levels. Th
data starts out as shown below.
Figure 1 a b c d e f g
1 Qty. cost level 1 cost level 2 cost level 3
2 1 $1.00 $1.00 $1.50 $1.50 $2.00 $2.00
3 $2.00 $2.00 $3.00 $3.00 $4.00 $4.00
4 $3.00 $3.00 $4.50 $4.50 $6.00 $6.00
5 $4.00 $4.00 $6.00 $6.00 $8.00 $8.00
6 $5.00 $5.00 $7.50 $7.50 $10.00 $10.00
7 $6.00 $6.00 $9.00 $9.00 $12.00 $12.00
8 $7.00 $7.00 $10.50 $10.50 $14.00 $14.00
9 $8.00 $8.00 $12.00 $12.00 $16.00 $16.00
10 $9.00 $9.00 $13.50 $13.50 $18.00 $18.00
From this point, I calculate cost level one as shown below:
Figure 2 a b c d e f g
1 Qty. cost level 1 cost level 2 cost level 3
2 1 =SUM(c2:c10)/a$2$$1.50 $1.50 $2.00 $2.00
3 $2.00 $3.00 $3.00 $4.00 $4.00
4 $3.00 $4.50 $4.50 $6.00 $6.00
5 $4.00 $6.00 $6.00 $8.00 $8.00
6 $5.00 $7.50 $7.50 $10.00 $10.00
7 $6.00 $9.00 $9.00 $12.00 $12.00
8 $7.00 $10.50 $10.50 $14.00 $14.00
9 $8.00 $12.00 $12.00 $16.00 $16.00
10 $9.00 $13.50 $13.50 $18.00 $18.00
I then do the same thing for cost level two and three (se
below):
Figure 3 a b c d e f g
1 Qty. cost level 1 cost level 2 cost level 3
2 1 $45.00 $1.00 =SUM(e2:e10)/a$2$ =SUM(g2:g10)/a$2$
3 $2.00 $3.00 $4.00
4 $3.00 $4.50 $6.00
5 $4.00 $6.00 $8.00
6 $5.00 $7.50 $10.00
7 $6.00 $9.00 $12.00
8 $7.00 $10.50 $14.00
9 $8.00 $12.00 $16.00
10 $9.00 $13.50 $18.00
I am trying to figure out how to get the formulas in columns D & F t
"reflect" the formulas in column B. If B2 is the sum of the cells tha
are one to the right and 9 down, then I want D2 and F2 to calculate th
sum of the cells one to the right and 9 down. If I delete A3, than
want D3 and F3 to be blank.
The details that haven't been mentioned yet are the following:
*The raw data shown in Figure 1 is dumped into this page from variou
other sheets in the spreadsheet.
*The number of variables used in the formulas column C (and E & G
varies. In this instance it is 9 (row #2 to row #9), but it could b
any number.
*I will repeat these steps multiple times in column B.
Any help you can provide would be greatly appreciated. Thank you
I frequently need to calculate assembly costs at different levels. Th
data starts out as shown below.
Figure 1 a b c d e f g
1 Qty. cost level 1 cost level 2 cost level 3
2 1 $1.00 $1.00 $1.50 $1.50 $2.00 $2.00
3 $2.00 $2.00 $3.00 $3.00 $4.00 $4.00
4 $3.00 $3.00 $4.50 $4.50 $6.00 $6.00
5 $4.00 $4.00 $6.00 $6.00 $8.00 $8.00
6 $5.00 $5.00 $7.50 $7.50 $10.00 $10.00
7 $6.00 $6.00 $9.00 $9.00 $12.00 $12.00
8 $7.00 $7.00 $10.50 $10.50 $14.00 $14.00
9 $8.00 $8.00 $12.00 $12.00 $16.00 $16.00
10 $9.00 $9.00 $13.50 $13.50 $18.00 $18.00
From this point, I calculate cost level one as shown below:
Figure 2 a b c d e f g
1 Qty. cost level 1 cost level 2 cost level 3
2 1 =SUM(c2:c10)/a$2$$1.50 $1.50 $2.00 $2.00
3 $2.00 $3.00 $3.00 $4.00 $4.00
4 $3.00 $4.50 $4.50 $6.00 $6.00
5 $4.00 $6.00 $6.00 $8.00 $8.00
6 $5.00 $7.50 $7.50 $10.00 $10.00
7 $6.00 $9.00 $9.00 $12.00 $12.00
8 $7.00 $10.50 $10.50 $14.00 $14.00
9 $8.00 $12.00 $12.00 $16.00 $16.00
10 $9.00 $13.50 $13.50 $18.00 $18.00
I then do the same thing for cost level two and three (se
below):
Figure 3 a b c d e f g
1 Qty. cost level 1 cost level 2 cost level 3
2 1 $45.00 $1.00 =SUM(e2:e10)/a$2$ =SUM(g2:g10)/a$2$
3 $2.00 $3.00 $4.00
4 $3.00 $4.50 $6.00
5 $4.00 $6.00 $8.00
6 $5.00 $7.50 $10.00
7 $6.00 $9.00 $12.00
8 $7.00 $10.50 $14.00
9 $8.00 $12.00 $16.00
10 $9.00 $13.50 $18.00
I am trying to figure out how to get the formulas in columns D & F t
"reflect" the formulas in column B. If B2 is the sum of the cells tha
are one to the right and 9 down, then I want D2 and F2 to calculate th
sum of the cells one to the right and 9 down. If I delete A3, than
want D3 and F3 to be blank.
The details that haven't been mentioned yet are the following:
*The raw data shown in Figure 1 is dumped into this page from variou
other sheets in the spreadsheet.
*The number of variables used in the formulas column C (and E & G
varies. In this instance it is 9 (row #2 to row #9), but it could b
any number.
*I will repeat these steps multiple times in column B.
Any help you can provide would be greatly appreciated. Thank you