• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer
EngineerExcel

EngineerExcel

FREE EBOOK
  • About
  • Course
  • Free Training
  • Resources
  • Login

Finding Optimum Solutions with Excel Charts

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

Primary Sidebar

Featured Posts

Save and Load Excel Solver Models

Create From Selection Excel Technique: Name Multiple Cells Quickly

Colebrook Equation Solver in Excel

Solving Non-Linear Equations in Excel with Goal Seek

Hyperbolic Curve Fitting in Excel

About Me: Charlie Young, P.E.

I’m a licensed professional engineer with a degree in Mechanical Engineering and over a decade of practical experience building engineering applications in Excel. My goal is to help you learn how to turn Excel into a powerful engineering tool.

If you’re interested in learning more, click the button below to receive an update whenever I have a new tip to share. I’ll also send you a copy of my free eBook “10 Smarter Ways to Use Excel for Engineering”.


Subscribe Now

Footer

SOCIAL

Keep up with EngineerExcel:
  • Email
  • RSS
  • YouTube
EngineerExcel

Free Course

Navigation

  • Home
  • About
  • Free Course
  • Excel Tips
  • Resources
  • Coupon
  • Login

Support

  • Support
  • Terms
  • Privacy

Copyright © 2021 EngineerExcel.com · Log in