Run Multi-Cell-Input Formula for Table of Values

Hyperlite

New Member
Joined
Jun 27, 2012
Messages
10
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.
 
Last edited:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Forum statistics

Threads
1,223,883
Messages
6,175,167
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top