Excel trick: circular references

Posted in hacks and kludges on Thursday, March 21 2013

I am not a big fan of using excel for doing complicated engineering calculations. I have spent way too much of my life trying to find the mistake another's convoluted spreadsheet, it is all to easy to make "spaghetti code" in a spreadsheet.

That said I'm now going to talk about a way to build super convenient spreadsheets that can simultaneously go off the rails in a spectacular fashion: using circular references.

You may be aware of circular references as an error given when cells self reference, but it is also a way to force excel to perform iterative calculations. The other big ways are by using goal seek or the solver. However if you have a lot of cells that need to use iteration to solve then using either goal seek or the solver can get really onerous. For example if you are attempting a mass balance and have recycle loops, then solving all the recycles over and over can be a pain.

The key is to think about what you want to calculate, x, and find a way to write it in the form $ x = f(x)$ or $ x_{k+1} = f(x_{k})$ where k is the order of iteration. In the options for excel, and Libre Office, you can turn on iterations and set the maximum number of iterations and the error tolerance, this forces excel to try and iterate those circular references. This doesn't go with the spreadsheet, though, so if you send it to someone else they will get a pile of error messages if they do not have iterations turned on.

An example: Suppose I have done some algebra and figured out the mechanical energy balance (or extended Bernoulli equation) for a pipeline. I know the pressure drops, elevation changes, work provided by the pump, and the dimensions of the pipeline and what I want to determine is the flow velocity (or flowrate).

The problem: the frictional losses in the pipe are a function of velocity, the fanning friction factor is a function of velocity. Well I can solve this iteratively in the following way:

  1. Guess a value for the friction factor, say $ f = 0.005$, I could get this by looking at a Moody diagram for my relative roughness and taking the limiting factor.
  2. Calculate the velocity from the mechanical energy balance, with that f I just guessed.
  3. Calculate a new friction factor, using the velocity I just figured.
  4. Rinse and repeat until the velocity stops changing.

This is a fixed-point iteration. I can put these cells into excel (or Libre Office) like so:

pumping problem

Here I use the Churchill correlation to give me the friction factor, and I have re-written my mechanical energy balance to be of the form $ v = v(f)$

To start the iteration I take the cell that is the initial guess of f and set it equal to the final calculated value of f:

Start iterations

When I hit enter excel iterates until the answer stops changing (or it hits the maximum number of iterations):

Done iterations

That is the velocity of the flow.

So why would I want to do this? For this particular example it would have been just as easy to use goal seek, or the solver. But suppose I wanted to plot the velocity of the fluid as a function of pump head, then what? Well you could tediously use the solver to calculate the velocity over and over again for each point in the plot. Alternatively you could do what I just did above in a column as a row and drag it down. Each row then iterates a solution for the velocity.

The example given above is for fixed point iteration, an alternative way of setting it up is to use Newton's method and circular references, which is something I might talk about in a future example.

tags: excel, pipe flow,