Building a flexible tool for mass balances in excel - any tips?

[MartinLe]

Hi all,
I often deal with mass balances in spreadsheets. They are not terribly complex, but almost always involve a bunch of separation steps with recirculation - that translates to circular calculations.

What I do is taking one or two key values, and adjusting them by the goal seeking function till the mass balance is correct. Are there better ways?

Now, I want to build a spreadsheet as a template that allows me to easily add components. My idea is to build blocks of lines that either do a separation or mixing of streams, every output stream is indexed and the input streams take this data by vlookup or similiar.
In theory I should be able to add operations to my mass flow by copying the blocks, adjusting the parameters and selecting the correct indices.
Did anyone have a similiar idea before and run into trouble, any pointers what i should take into account from the beginning?

all help is appreciated!

 

[davidbeach]

Options|Formulas - Enable iterative calculation and set a reasonable max number of iterations and the maximum change. You might then also want to set Manual calculation if each round of calculations takes too long so that it only recalcs when you need a new answer rather than for every cell change.

 

[electricpete]

What I do is taking one or two key values, and adjusting them by the goal seeking function till the mass balance is correct. Are there better ways?

Goal seek manipulates one cell at a time to achieve a target. Solver can manipulate multiple cells to achieve a target (that target could be a single-cell objective function which is a sum of squares or sum of absolute values of multiple other objective functions that are each to be minimized). I'd think solver would be better although I don't fully understand the math problem you're solving.

=====================================
(2B)+(2B)' ?

 

[GregLocock]

My solution is to define target ranges for each response, and then use a quadratic of the current value's fit inside or outside those upper and lower limits, and a weighting factor, to give a score for each response. Then sum them and that gives an overall score for that particular iteration. Solver is then used to minimise the overall score. Sometimes I set it so that any value within the limits counts as zero, other times I prefer to leave it centre biased.

If Solver then comes up with an 'inappropriate' solution I reexamine my weightings to bias the answer in the direction I want.

Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376

http://eng-tips.com/market.cfm?

 

[MartinLe]

Solver - I was not aware of this tool and I'm working with it right now to solve a mass balance - thanks for the tip, all!
The approach I'm trying right now is minimising a sum of squares.

Iterative solving - I want to avoid this, because the mass balance will be integrated into a larger tool where turning off automatic calculations will cause inconveniences that I want to avoid.

To explain the math: I have a reactor vessel in which I mix a fresh substrate stream and another stream that I call recirculation. Gasification happens, TS (solids) gets converted to biogas. The liquid output gets fed into a separator, with two output streams - a solid one with less water, a liquid one with less solids. Part of the latter will be pumped back into the vessel as recirculation. Now the solids content of the liquid stream depends on the input into the separation step, wich also depends on how much I recirculate. So far this is solvable by simply adjusting the solids content after the reactor vessel with the goal seek function.
Now I sometimes need to add further separation steps, and I think I want some flexibility so I can adjust my tool down the road if other process flows show up. I hope I made the problem I try to solve more clear?