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:
[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]QTY[/TD]
[TD]Material Cost[/TD]
[TD]Total Cost[/TD]
[TD][/TD]
[TD]QTY[/TD]
[TD]Material[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10[/TD]
[TD]300[/TD]
[TD]Answer from E10[/TD]
[TD][/TD]
[TD]Input from A2:A6[/TD]
[TD]Input from B2:B6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]25[/TD]
[TD]290[/TD]
[TD]Answer from E10[/TD]
[TD][/TD]
[TD]Labor Rate[/TD]
[TD]Machine 1 Setup[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]50[/TD]
[TD]280[/TD]
[TD]Answer from E10[/TD]
[TD][/TD]
[TD]25[/TD]
[TD]1[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]100[/TD]
[TD]265[/TD]
[TD]Answer from E10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]200[/TD]
[TD]250[/TD]
[TD]Answer from E10[/TD]
[TD][/TD]
[TD]Time per Unit[/TD]
[TD]Machine 2 Setup[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Total Cost[/TD]
[TD]Machine 3 Setup[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Answer[/TD]
[TD]1[/TD]
[TD]500[/TD]
[/TR]
</tbody>[/TABLE]
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.
[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]QTY[/TD]
[TD]Material Cost[/TD]
[TD]Total Cost[/TD]
[TD][/TD]
[TD]QTY[/TD]
[TD]Material[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10[/TD]
[TD]300[/TD]
[TD]Answer from E10[/TD]
[TD][/TD]
[TD]Input from A2:A6[/TD]
[TD]Input from B2:B6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]25[/TD]
[TD]290[/TD]
[TD]Answer from E10[/TD]
[TD][/TD]
[TD]Labor Rate[/TD]
[TD]Machine 1 Setup[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]50[/TD]
[TD]280[/TD]
[TD]Answer from E10[/TD]
[TD][/TD]
[TD]25[/TD]
[TD]1[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]100[/TD]
[TD]265[/TD]
[TD]Answer from E10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]200[/TD]
[TD]250[/TD]
[TD]Answer from E10[/TD]
[TD][/TD]
[TD]Time per Unit[/TD]
[TD]Machine 2 Setup[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Total Cost[/TD]
[TD]Machine 3 Setup[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Answer[/TD]
[TD]1[/TD]
[TD]500[/TD]
[/TR]
</tbody>[/TABLE]
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: