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.
It has been said before many times to start at the beginning and finish at the end. For most chemical engineering problems, there is a natural sequence that starts with basic data and proceeds step-by-step to a final result. However, in many calculations, you may need to find one or more starting values that yield a desired final result, or a target value, as shown below. The target may be a specific value, or it could be the minimum or maximum of a function, such as cost or profitability. The calculation may have more than one input cell, and there may be constraints on various elements of the calculation.
For one-time solutions of these targeting problems, you can often simply adjust the input value by trial-and-error and meet the target after only a few tries. Excel offers two tools that automate this procedure: Goal Seek and Solver. (The Solver is an add-in provided by Frontline Systems. For information and guidance on using the Solver, see www.solver.com.)
Excel’s Goal Seek is only able to solve target value problems. It is a black-box tool that does not give the user options or control over its numerical procedure. For example, we want to determine the liquid depth in a 4-m-dia. spherical tank that corresponds to a volume of 10 m3. The formula is:
where V is the volume, h is the liquid depth in the tank, and Rd is the radius of the tank. Below, we set up a calculation on the spreadsheet based on a test value of 2 m for the depth. Walking through the steps below, note that the total volume of a liquid in a tank is calculated for an arbitrary liquid height of 2 m (a) by the formulas shown in (b). Use Goal Seek to set the volume equal to 10 m3 by changing cell h (c) to find the depth corresponding to a 10-m3 volume (d).
Invoke Goal Seek from the What-If Analysis drop-down list in the Data Tools group of the Data tab of the Ribbon. Complete its fields (as shown in c above), by setting cell V equal to 10 m3 by changing cell h. Upon clicking the OK button and accepting the result, we have the solution that h = 1.45 m (as shown in d above).
The Solver is a more versatile tool that, in addition to solving target value problems, can also solve optimization problems and incorporate constraints. You also have more control of the numerical method used and parameters such as the convergence criterion.
In this example, we use Solver to determine the optimum size of a bin with a cylindrical upper section, a conical lower section, a defined volume, and a minimum bin angle to exceed the angle of repose of the granular material. The optimum is defined as a minimum surface area or area of material used in constructing the bin for a volume of 5 m3.
The required formulas are:
where Vcyl is the volume of the cylindrical upper section, r is the radius, and hcyl is the height of the cylindrical upper section,
where Vcon is the volume of the conical lower section and hcon is the height of the cone,
where Vbin is the bin volume,
where θ is the conical angle,
where Acyl is the cylindrical area,
where Atop is the area on the top of the cylinder,
where Acon is the conical area,
where Abin is the total bin surface area.
The constraints are defined as:
Vbin = 5 m3 and θ ≥ 30 deg. = π/6.
The starting spreadsheet with a trial calculation is shown in figure a. below, followed by the associated formulas in b. In figure c. below, Solver is used to satisfy the constraints and find the minimum value.
The bin volume does not meet the specification of 5 m3 (although the bin angle is greater than 30 deg.). Thus, this is not the optimal solution. Next, specify the Solver Parameters, setting the value of the bin surface area (Abin) to a minimum by changing the diameter, and the heights of the cylindrical (hcyl) and conical (hcon) sections. Then, input the constraints:
Although these are elementary examples, they do demonstrate the importance of setting up calculations in their natural order and using one of the targeting tools to obtain a solution. This is in contrast to reformulating the problem mathematically into one or more nonlinear equations that you would solve, or attempt to solve, using an analytical or numerical method. Although an applied mathematician (or perhaps a chemical engineering faculty member) may prefer to use an analytical or numerical method, using Solver or Goal Seek is more realistic in the world of the practicing chemical engineer.
More tips and techniques
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.