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.

[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.]

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.

[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 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.

[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.]