INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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!

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

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.

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

Quote:

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)' ?

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

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: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?

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

(OP)
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?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close