Two independent variables, want function output in matrix form.

[tilbince]

Hello,

I'm trying to do a process analysis based on the varying flow rates of two different streams. The first: (20, 30, 40, 50) and the second: (0, 80, 120) - units are the same. I want to set up the analysis so flow rates are the column and row titles of a matrix and the matrix is populated with the calculated costs for each combination.

I'm running into a wall because I don't want to have to copy the equations through 12 times. Basically, I want to have my inputs be the two vectors and the eventual output be a 3x4 matrix.

I've been looking through other threads and researching online but am having trouble understanding exactly how to approach this problem in Excel (are arrays an option?); I'm not experienced in Excel programming. VBA suggestions are welcome because I need to learn sometime! lol

Any solutions or previously solved threads out there?

Thanks!

[Søren Larsen]

I think this can be done without array formulas and macros. But how are costs calculated?

[FDibbins]

if you want what i think you want, then the following will maintain its relative row and col references when you copy it across and down your table....

=D$5*$B6

it it based on data in col B applied to data in row 5

Let me know if this is what you need, or maybe upload a sample to show your requirements and results?

[tilbince]

Costs are calculated through a series of mass and energy balances to determine utility usage (electricity, cooling water, etc). Flow rates also determine the operating hours (thus employee wages) of the plant based on the plant capacity.

This is why I was looking for a way to avoid repeating the calculation in a new cell for each combination. Does that all make sense?

[Søren Larsen]

Yes it makes sense. I guess we can't provide you with an exact formula, since we don't precisely how it's calculated. But using absolute references ($), as FDibbins suggests, should enable you to write the formula in one cell, and then copy it to the rest of your table.

[tilbince]

I understand how that would work but wouldn't I have to use absolute references in the same way for each of the other 40 or so calculations to get the final cost sum to work the same way?

The solution I had in mind more resembled a for loop. I learned to program in Matlab, so with that syntax here's what I was thinking

[not real code]
A=(20,30,40,50)
B=(0,80,120)
C=zeros(3,4)
FOR a=1:4
FOR b=1:3
~materials, energy, labor calculations~
C(b,a)=(sum of costs)
END
END
[/not real code]

[Søren Larsen]

Could you explain how the first cell (A=20, B=0) is calculated and what the answer should be?

[FDibbins]

the way i wrote that formula, you can copy it across and down, with no "manual" changes needed. it will autmatically adjust to reference the relevant cells. give it a shot and see

[JosephP]

sounds to me like you want a 2 variable data table. check the help for samples but you basically set up the formula in the top left column of the table and the table substitutes the row and column values for each result.