Share your tips, on Engage!
Do you have some tips of your own about spreadsheets? If you're a member, I'd love you to share them on AIChE Engage.
One of the main reasons chemical engineers became attracted to spreadsheets was for their ability to carry out flowsheet calculations. A process with a recycle stream involves a circular calculation that cannot proceed automatically because of its iterative nature. In a more general sense, many process calculations, when laid out in logical sequence, involve circular calculations.
In such a situation, a value is needed to continue the calculation, but that value is calculated later in the scheme. The strategy for solving such calculations is to specify a starting value and then “recycle” the value calculated later until the loop converges. It is possible in some cases to reformulate this problem mathematically to eliminate the circular calculation, but that leaves the scheme in an unnatural format that is more difficult to manage and understand.
Circular calculations that use a simple substitution method do not always converge, but in chemical engineering problem-solving they commonly do converge. For cases of nonconvergence, numerical methods are available, notably that of Wegstein, that may force convergence.
I suggest taking advantage of Excel’s Iterative Solver to converge circular calculations. As a simple example, we can use the Colebrook equation relating the Moody friction factor (fM), Reynolds number (Re), pipe inside diameter (Di), and pipe roughness (ε) for turbulent flow:
Notice that in order to compute fM, we need a value of fM for the right-hand side of Eq. 10. Therefore, a simple, analytical solution is not available. So, a circular calculation arises. Below is a spreadsheet that arranges this calculation, with the underlying formulas, which follow beneath the spreadsheet.
This calculation has not converged because the sqrt(fM) does not equal the Colebrook sqrt(fM). So, we expand the spreadsheet to include the option to “close the loop” by a modification of the sqrt(fM) formula:
Now, when Reset is FALSE, the loop is closed. However, Excel complains that there is a circular path:
To resolve this, set up the Iterative Solver via File ⇒ Options ⇒ Formulas:
The Maximum Iterations was set to 10 because, based on our experience with manual recalculation, this guarantees convergence of the circular calculation to the desired precision. The result is:
The calculation also updates automatically if another parameter is changed, such as setting Re = 50,000, used in the spreadsheet below. So, as circular calculations arise in your chemical engineering problem-solving, embrace them and manage them with the Iterative Solver.
If you're just joining us, check out the entire series. And if you want a full crash course instead of just helpful tips, you should check out the AIChE Academy's "Spreadsheet Problem-Solving for Chemical Engineers," where these tips come from, and also check out the other Excel courses available through the AIChE Academy at www.aiche.org/academy.
Want more Excel tips for chemical engineers?
If you know you want to delve even deeper than this blog series – or if our Excel tips leave you hungry for more – be sure to check out AIChE’s virtual combo course on spreadsheet problem solving and VBA programming. It’s taught by David E. Clough, the author of this series, and combines two of AIChE’s most popular spreadsheet courses, Spreadsheet Problem-Solving for Chemical Engineers and Excel VBA Programming for Chemical Engineers.
Share your tips, on Engage!
Do you have some tips of your own about spreadsheets? If you're a member, I'd love you to share them on AIChE Engage.
This Excel spreadsheet series is drawn from an article by David Clough that appeared in AIChE's CEP Magazine. You can find the current issue and an extensive archives of back issues at www.aiche.org/cep.