Using Excel to Optimize the Surface Area of Cylinder

In the Chapter 11 assignment on surface area and volume of cylinders, the last question asked students to calculate the optimal dimensions of a can of soda in order to minimize the amount of aluminum used. Here is the original question:

You are in charge of creating a new soft drink can. This can needs to hold 200 cm3 of liquid.

(a) The radius of one cylindrical can is 2 cm. The radius of the other is 5 cm. What would the height and surface area of the can be in each case? Are the dimensions of the cans suitable? Explain your answer.

(b) In order to save money, the can needs to be designed so that it requires the smallest quantity of aluminum. Find the radius and height of the can that does this, to two decimal places.

This question can be done algebraically. However, when we did the question, students found it difficult to check their work. This is where Excel comes in. While there are other math programs out there that are specifically designed to do symbolic math, the advantage of Excel is its ubiquity. And if you can't find Excel where you are, there are a number of online spreadsheets that do everything we will need to do in this question.

Bored already? Download the spreadsheet here: SurfaceAreaOfCan

Or download it as a PDF here: SurfaceAreaOfCan


The basic idea is this:

  1. Put four columns into Excel: Radius, Volume, Height and Surface Area
  2. Make the radius vary from 2 to 5, using whatever step you want (I used "0.1" on the left, then "0.01" on the right)
  3. Calculate the height, based on the radius: $latex h = \frac{V}{\pi r^2}$
    • For this, you can use the number 200, since you know the volume, or you can insert the cell value that contains the number 200
    • My Excel formula for height looked like this: =B2/(PI()*A2^2)
    • In that formula, "PI()" means $latex \pi$, "B2" and "A2" refer to their respective cells, and the "=" sign means to calculate the value
  4. Calculate the surface area based on that.
    • The surface area of a cylinder is $latex SA_{cylinder} = 2 \pi r^2 + 2 \pi r h$.
    • In Excel, it looks like this: =2*PI()*A2*C2+2*PI()*A2^2
  5. Once that's done, highlight the cells you want to continue, put your cursor in the bottom-left corner of the box, and drag it down. If done correctly, Excel should auto-fill all the values below.
  6. Finally, if you want to make a graph out of it, highlight the "Radius" and "Surface Area" columns (use the Ctrl button to select the separate data sets), and click on "Insert", "Recommended Charts".

Once that's done, all that's left is to look through your data to see where the Surface Area hits its minimum.