You'd like to solve a nonlinear equation that requires an iterative solution and are not sure how to approach it in Excel.
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.
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 ...