I have a cost module at work that has multiple linked cells that take different inputs to spit out a final variable cost. I can input individual values to get the cost, but would like to figure out how to have a range of cell values be ran through the multiple cell formula. Please see example data below:
<tbody>
</tbody>
Above is a very simplified version of what I am trying to do. I have a multi cell formula that utilizes inputs into Cells E2 and F2 to generate an output value in Cell E10. I would like to write a formula in Cells C2:C6 to run the given values in A2:A6 and B2:B6 through the input cells at E2 and F2 and return the answer from E10 in Cells C2:C6.
The formula in E10 is as follows: =((E4*E7)*E2)+(F2*E2)+((F4*G4)+(F7*G7)+(F10*G10))
The idea would be to input data into Cells A2:A6 and B2:B6 and Cells C2:C6 would auto populate based on the formula in Cell E10, assigning Cells E2 and F2 to the appropriate value from A2:A6 and B2:B6.
I have tried nested if statements with and statements and nothing is working. I am unsure if this requires VBA code. It should be noted that due to the complexity of the cost module calculation, coding a version of the E10 formula into C2:C6 is not possible.
| A | B | C | D | E | F | G | |
| 1 | QTY | Material Cost | Total Cost | QTY | Material | ||
| 2 | 10 | 300 | Answer from E10 | Input from A2:A6 | Input from B2:B6 | ||
| 3 | 25 | 290 | Answer from E10 | Labor Rate | Machine 1 Setup | Cost | |
| 4 | 50 | 280 | Answer from E10 | 25 | 1 | 300 | |
| 5 | 100 | 265 | Answer from E10 | ||||
| 6 | 200 | 250 | Answer from E10 | Time per Unit | Machine 2 Setup | Cost | |
| 7 | 10 | 0 | 400 | ||||
| 8 | |||||||
| 9 | Total Cost | Machine 3 Setup | Cost | ||||
| 10 | Answer | 1 | 500 |
<tbody>
</tbody>
Above is a very simplified version of what I am trying to do. I have a multi cell formula that utilizes inputs into Cells E2 and F2 to generate an output value in Cell E10. I would like to write a formula in Cells C2:C6 to run the given values in A2:A6 and B2:B6 through the input cells at E2 and F2 and return the answer from E10 in Cells C2:C6.
The formula in E10 is as follows: =((E4*E7)*E2)+(F2*E2)+((F4*G4)+(F7*G7)+(F10*G10))
The idea would be to input data into Cells A2:A6 and B2:B6 and Cells C2:C6 would auto populate based on the formula in Cell E10, assigning Cells E2 and F2 to the appropriate value from A2:A6 and B2:B6.
I have tried nested if statements with and statements and nothing is working. I am unsure if this requires VBA code. It should be noted that due to the complexity of the cost module calculation, coding a version of the E10 formula into C2:C6 is not possible.
Last edited:
