O'Reilly logo

Excel Scientific and Engineering Cookbook by David M Bourg

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

9.2. Solving Nonlinear Equations Iteratively

Problem

You'd like to solve a nonlinear equation that requires an iterative solution and are not sure how to approach it in Excel.

Solution

Use Goal Seek or Solver. See the introduction to this chapter for more information on how these two tools differ as well as their respective advantages and disadvantages.

Discussion

In this recipe, I want to show you by way of example how to use Excel's built-in Solver and Goal Seek tools to solve a nonlinear equation. The equation we'll consider is:

This equation is used to estimate a frictional drag coefficient, C f , as a function of the Reynolds number , R N , for some ship resistance calculations. There are other equations for this same purpose but I chose this classical one because it can't be written in an explicit form of y = g(x). Instead, you have to resort to some iterative method to find a value of C f corresponding to some given value of R N .

I'll show you how to use both Goal Seek and Solver to solve this example problem. In both cases the equation must first be rearranged in the following form:

Now we can use Solver or Goal Seek to iteratively find a value for C f given a value for R N that makes the righthand side of this equation equal to 0. But first, you need to enter the righthand ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required