In this post, you’ll see how to apply the Solver to the soda can optimization problem. Rather than optimizing the dimensions of the can graphically, we can use the Solver to find a more exact solution.
Again, we’ll set the diameter as the independent variable and calculate the length and surface area from it. Then the Solver will adjust the diameter to obtain the smallest possible surface area.
Open Worksheet 06g. Since the Solver will change the diameter later, you can start out with any reasonable guess. Enter 2 in cell C8. Then, assign the name D to that cell using the name box.
In the cell below, enter the formula for length:
    =4*V/(PI()*D^2)
Name that cell L.
In the cell for surface area, enter the formula:
    =PI()/2*D^2+PI()*D*L
Now you’ll have a guess value for the diameter and the corresponding length and surface area:
You could guess values of the diameter to find the smallest surface area, but Solver will do that optimization for you. Go to the Data tab and choose Solver on the right side of the ribbon.
The first input is the objective, which is the outcome that we want to monitor. This needs to be a cell containing a formula. In this case, the objective will be the surface area, so select that cell.
The next step is to choose whether you want to maximize the objective, minimize it, or set it to a specific value. For this problem, choose Min for minimize.
The variable cell that we want to change is the diameter. Remember that the variable cells can only contain values. In this problem, the diameter is the independent variable by which the length and surface area are calculated, so the diameter is the only cell that Solver will need to adjust. Select the diameter.
The only constraint that’s necessary in this problem is to force the variable to be a positive number. There’s a checkbox below the constraints window to ensure the variable is positive: 
However, for illustration purposes, let’s add an additional constraint to force the diameter to be less than ten. Click Add. In the window that opens, select the diameter. The second menu will have less-than-or-equal-to (<=) selected by default, so just add the constraint of 10 in the last box.
Click OK to store the constraint.
The last step of the Solver is to set the algorithm. The default setting of GRG nonlinear is fine for this problem. We saw previously that this is a smooth problem when we graphed the surface area versus diameter. There isn’t anything in the formula that would cause a discontinuity such as an IF function or an absolute value function. One issue that can arise with the GRG nonlinear algorithm is that it may give you a local minimum rather than a global minimum, but for this function, there is only one minimum.
Now that everything is set up, click Solve. When the solver is finished running, it brings up the Solver results window:
At this point, you can choose to keep the solution that Solver found, or set the variable cells back to their original values by clicking Restore Original Values. The values that Solver found are shown in the worksheet simultaneously:
If you didn’t have a variable constrained properly or if something else went wrong with the solution, you can restore back to the original values and try again. In this case, it appears to have worked as expected, so accept the solution by clicking OK.
You may have noticed that the result from the Solver is similar to the result that the graphical method yielded, but with a finer resolution. The graphical method gave an optimal diameter of 3″ and an optimal length of 3.1″. We used a fairly coarse resolution of 0.25″ in that example. The Solver is able to get a more accurate result because the resolution is much finer. In this case, it found the smallest surface area when the diameter and the length are equal.
Of course, soda cans are not these dimensions. As mentioned previously, the assumptions that were made for this problem can explain why. Soda cans aren’t perfect cylinders, and the cost isn’t proportional to the surface area in real life.
The Solver has some additional features worth mentioning. If you re-open the Solver, you’ll see that all of the previous settings have been saved. These will be saved even if the workbook is closed and reopened.
In some cases, you may want to save a particular set of solver inputs and try other inputs. You may also have more than one optimization problem in a worksheet and you want to save the setups for each. To do so, click the Load/Save button. The window that pops up will allow you to save the settings on the worksheet itself.
The settings will take up five cells, so select a range of five blank cells and click Save. Excel will write information to those cells.
Close the Solver and examine the formulas in each cell. The cells contain, in this order:
- A formula that tells solver to minimize the value of cell C10: =MIN(C10)
- The location of the variable cell: =COUNT($C$8)
- The constraint for the diameter to be less than 10: =D<=10
- Two settings for the solver engine
If you re-open the Solver and make changes to the settings, you can later reload the previous settings. Within the Solver, click Load/Save. Select the range that contains the saved settings and click Load. Excel will ask you if you want to replace the current model or merge with it. Choose Replace, and your original settings will be restored.
You can save your settings when you have more than one optimization to perform, or if you’re trying different Solver settings to improve the result. You can add labels or comments to the cells containing the saved settings so that you can find them easily later on.
Solver is a very powerful tool, and we’ve only scratched the surface of what you can do with it. In the chapter on curve fitting, you’ll see how to use Solver to do regression analysis, which will give you more practice with this tool.
