Home |
Search |
Today's Posts |
#1
|
|||
|
|||
relective formula
Does anyone know how to make a formula “reflective” of another formula? I frequently need to calculate assembly costs at different levels. The 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 (see 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 to "reflect" the formulas in column B. If B2 is the sum of the cells that are one to the right and 9 down, then I want D2 and F2 to calculate the sum of the cells one to the right and 9 down. If I delete A3, than I 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 various 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 be any number. *I will repeat these steps multiple times in column B. Any help you can provide would be greatly appreciated. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Build formula using field values as text in the formula referencing another workbook | Links and Linking in Excel | |||
Regression Leverage Formula (Jerry W. Lewis or Mike Middleton)already have DFITS formula | Excel Worksheet Functions | |||
extract formula result form cell without running formula again | Excel Programming | |||
Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
Commenting custom formula fields/formula on formula editor | Excel Programming |