The next few sections will show you some ways to perform design optimizations in Excel. The first method will be graphical optimization. This method works well when there are only one or two independent variables.
Worksheet 06f contains an optimization for the dimensions of a soda can to minimize its cost. We’ll assume that the can is a perfect cylinder and that cost is proportional to the surface area.
This can will contain 12 fluid ounces, or 22 in3 of liquid. Essentially, you’ll be optimizing the dimensions of a cylinder to contain this volume, minimizing the surface area. For this example, set the diameter as the independent variable. The length of the can (L) can be calculated from the diameter (D) and the volume (V):
[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]
The surface area can then be calculated from the length and diameter:
After doing those calculations, you can graph the surface area against the diameter to see which diameter minimizes the surface area. Therefore, this is a graphical optimization.
Set up your spreadsheet to contain a range of diameters from 0.5″ to 4″ in increments of 0.25″. To do this, you can just enter the first two values, select them, and then drag the fill handle down until you get to 4″.
Select the array of diameters and name it D using the name box. You can use an array formula to calculate the cylinder lengths. Select the cells the lengths will go into, and enter the formula:
=4*V/(PI()*D^2)
Type Ctrl-Shift-Enter to enter the array formula. Name the array of lengths L.
[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]
Select the cells for the surface area and enter the formula:
=PI()/2*D^2+PI()*D*L
Use Ctrl-Shift-Enter again.
Now that you have the diameter, length and surface area for a range of cylinder sizes, you can use a chart to find the diameter that minimizes the surface area. Select the cells containing the diameter, then hold Ctrl and select the cells containing the surface area. (Holding Ctrl allows you to select non-adjacent columns.) Go to Insert > XY scatter chart or use your Quick Access Toolbar to make a chart:
The chart shows that a small diameter results in a very large surface area because the cylinder has to be very long to contain the volume. As the diameter increases, the surface area decreases. If you look closely, it actually starts to increase again. To find the minimum, you can adjust the y-axis scale. Right-click the y-axis and select Format Axis. Set the minimum to 40 and the maximum to 60. The minimum is easy to see on the resulting chart:
The minimum appears to be about 3. If you look at the table, you can see the surface area is lowest when the diameter is 3″ and the length is 3.1″. This appears to be a good estimate given the resolution of the independent variable.
In reality, soda cans are not 3″ in diameter and 3.1″ tall. This result can be attributed due to our assumptions. A soda can is not a perfect cylinder, and the cost is probably not exactly proportional to the surface area. However, as an exercise, it’s a useful demonstration.
In the next section, you’ll learn about Excel’s Solver, a tool that can be used for optimization problems like this, among other things.
[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]