What are you working on?

Find a calculator, explore a formula, or learn the method.

THE ENGINEERING REFERENCE / GUIDE

Excel Goal Seek for Engineering Equations: A Reproducible Worked Example

Short Answer

Excel's Goal Seek solves implicit engineering equations by iteratively changing one input cell until a formula cell reaches a target value. This article explains the three-input workflow and walks through a reproducible Colebrook friction-factor example with exact cell formulas. The same four-step pattern extends to pipe sizing, thermal-fluid calculations, and circuit-design equations.

Engineers routinely meet equations that cannot be rearranged: the unknown sits on both sides, hides inside a logarithm, or is otherwise impossible to isolate algebraically. The Colebrook equation for pipe friction factor is the classic case. Excel’s Goal Seek feature is built for exactly this problem: rather than deriving a closed-form solution, you build the equation in worksheet cells, tell Excel which cell to drive to a target value, and let the program adjust a single input automatically. This article explains how Goal Seek works and walks through a fully reproducible worked example based on the Colebrook friction-factor equation, using the setup documented in engineering spreadsheet guides and reused widely for pipe-flow, thermal-fluid, and design calculations.

What Goal Seek Does

Goal Seek is one of Excel’s What-If Analysis tools, located on the Data tab. As Aubrey Kagan explains in an EDN article on solving design equations, the principle is simple: the tool changes the value of one worksheet cell step by step until a second, formula-driven cell meets a condition you set. You provide three inputs: the Set cell (the formula cell whose result you want), the To value (the target result), and the By changing cell (the single input Excel is allowed to adjust).

The key advantage is that the unknown never has to be isolated. Kagan notes that Excel’s most basic use requires you to express the variable as x equals everything else, and that this rearrangement is frequently difficult or impossible for design equations; Goal Seek removes the requirement entirely. Behind the interface, Excel runs an iterative search, repeatedly refining the changing cell until the set cell lands within tolerance of the target. By default Excel allows up to 100 iterations and stops when the target is matched to within 0.001 of maximum change; both limits can be tightened under File → Options → Formulas. The feature has been part of Excel for many years, and Kagan’s walkthrough, developed with Excel 2010, observes little difference across versions.

Why Implicit Equations Are Common in Engineering

Many standard engineering relations are implicit. Bengtson’s pipe-flow spreadsheet guide notes that Darcy-Weisbach and friction-factor calculations, used with the Moody (Darcy) friction factor or the Fanning friction factor for frictional head loss and pressure drop in pressure piping, often require a graphical or iterative solution.

The friction factor itself comes from the Colebrook equation:

1/√f = −2·log10( ε/(3.7·D) + 2.51/(Re·√f) )

Here f is the Darcy friction factor, ε (epsilon) is the pipe’s absolute roughness, D is the diameter, and Re is the Reynolds number. The unknown f appears on both sides, under a square root on the left and inside the logarithm on the right, so no algebraic rearrangement isolates it. Engineers traditionally read f from a Moody chart or iterate by hand; Goal Seek performs the same iteration automatically in a fraction of a second. The Colebrook equation is therefore the standard demonstration problem, and the worked example below follows the setup described in the EngineerExcel guide to Goal Seek, in which named cells hold the variables and separate formula cells evaluate each side of the equation.

A Reproducible Worked Example: the Colebrook Friction Factor

To make the example concrete, suppose a pipeline has a Reynolds number Re = 100,000 and a relative roughness ε/D = 0.0001 (for instance, ε = 0.0001 m with D = 1 m, both expressed in metres, since ε/(3.7·D) must be dimensionless). These are illustrative values included so the example can be reproduced exactly; substitute data from your own design. A reasonable starting guess for turbulent pipe flow is f = 0.03, the value the EngineerExcel walkthrough uses.

Step 1 — Set Up Inputs and Named Cells

On a fresh worksheet, enter the known quantities in separate cells: the Reynolds number (100000), the absolute roughness (0.0001), and the diameter (1; for a circular pipe the hydraulic diameter DH equals the pipe diameter). Assign each a name — Re, eps, and DH — using the Name Box to the left of the formula bar, as the EngineerExcel article does. In another cell, type the initial guess 0.03 and name it f. Naming the cells lets the formulas read like the printed equation. Make sure the changing cell holds a plain number, not a formula, because Goal Seek needs to overwrite it.

Step 2 — Evaluate Both Sides and the Residual

In two adjacent output cells, compute the left and right sides of the Colebrook equation. In the first, enter =1/SQRT(f). In the second, enter =-2*LOG10(eps/(3.7*DH)+2.51/(Re*SQRT(f))). Together these reproduce the equation exactly as written, using the same left-side and right-side formulas the EngineerExcel article specifies.

Now create a residual cell that subtracts one side from the other — for example =C10-C11 — and format it in scientific notation. Scientific formatting matters: a residual of 3.5E-04 looks like zero in ordinary decimal formatting, and the whole method depends on seeing how close the two sides are. At the initial guess of 0.03 the residual will not be zero; that mismatch is what Goal Seek will eliminate.

Step 3 — Run Goal Seek

Open Data → What-If Analysis → Goal Seek and complete the three fields: set the Set cell to the residual cell, the To value to 0, and the By changing cell to f. Excel then searches for the friction factor that makes the two sides equal. For the example inputs, the solution of the Colebrook equation is f ≈ 0.0185.

Step 4 — Verify the Result

Check the residual cell: after a successful run it should display a value effectively at or near zero, such as 0.00E+00 or a small power of ten. The exact residual depends on Excel’s iteration settings, so if you need a tighter match, run Goal Seek again from the converged value — repeating the command from an already-close guess typically polishes the result. As an audit, evaluate both sides of the equation at the converged f: for this example each side comes to roughly 7.35. If the residual is larger than your workflow tolerates, tighten the convergence settings under File → Options → Formulas.

Generalizing the Pattern to Other Equations

The Colebrook example is really a four-step recipe that transfers to any implicit equation. First, name the known inputs and place the unknown in a plain-value cell. Second, write the equation so that one residual cell computes left side minus right side, or whatever form of the equation should equal zero. Third, run Goal Seek, driving the residual to 0 by changing the unknown. Fourth, verify the residual and sanity-check the answer against physical intuition or a chart. The same skeleton serves the thermal-fluid calculations demonstrated in El-Awad’s paper in Spreadsheets in Education, and the circuit-design equations Kagan treats in EDN, where a component value must satisfy a relation that cannot be isolated.

Where Engineers Apply Goal Seek

  • Pipe-flow sizing. Bengtson’s guide describes Excel spreadsheets that calculate pipe flow rate for a given diameter and the required pipe diameter for a target pressure drop, both of which require iterative solutions; Goal Seek is one convenient way to run that iteration inside the worksheet.
  • Thermal-fluid analysis and teaching. El-Awad documents Goal Seek as an educational tool for basic thermal-fluid engineering analyses, letting students explore problems that would otherwise demand manual trial and error.
  • Circuit and component design. Kagan applies the tool to design equations that arise in electronics work, where the unknown appears on both sides of the governing relation.

Practical Tips and Common Pitfalls

  • Keep units consistent. The roughness ratio ε/(3.7·D) must be dimensionless, so ε and D need the same length units; verify that Re is computed from consistent units as well.
  • Choose a sensible starting guess. Goal Seek begins from whatever value is in the changing cell. A poor start can slow convergence or settle on an implausible root; 0.03 is a reasonable starting friction factor for turbulent flow.
  • Structure the cells correctly. The changing cell must contain a value, and the set cell must contain a formula that depends on it directly or through intermediate cells. If that dependency is broken, Goal Seek cannot proceed.
  • Watch the residual, not decimal rounding. Format the residual in scientific notation so tiny nonzero values remain visible rather than masquerading as zero.
  • Heed the failure message. If Excel reports that it may not have found a solution, the usual causes are weak convergence from the starting value, a discontinuity in the function, or hit iteration limits. Improve the initial guess, split the calculation into intermediate cells, or raise the iteration cap.
  • Beware multiple roots. When an equation has more than one solution, Goal Seek converges to whichever root is nearest the starting value, so confirm the answer makes physical sense.

Limitations and When to Move Beyond Goal Seek

Goal Seek solves for exactly one unknown against one target value with no side constraints, and it behaves most reliably when the target response is smooth and monotonic near the solution. For problems needing several changing cells, constraints, or optimization, Excel’s Solver add-in extends the same idea. For independent cross-checks, the EngineerExcel guide notes that nonlinear equations such as Colebrook can also be solved graphically, a useful verification of any Goal Seek result. For complete pipe-flow workflows, dedicated calculation spreadsheets such as those outlined in Bengtson’s guide may be more appropriate.

Practical Takeaways

Goal Seek turns implicit engineering equations into a compact spreadsheet routine: name the inputs, evaluate both sides of the equation, drive a residual to zero by changing the unknown, and verify the outcome. With the formulas above, a starting guess of 0.03, and the illustrative conditions Re = 100,000 and ε/D = 0.0001, the Colebrook example is fully reproducible — and the identical pattern extends to pipe sizing, thermal-fluid balances, and design equations across engineering disciplines.

FAQ

What does Goal Seek do in Excel?

It iteratively changes one input cell until a dependent formula cell reaches a value you specify, letting you solve equations without isolating the unknown algebraically. You supply three inputs: the Set cell, the To value, and the By changing cell.

Can Goal Seek solve the Colebrook equation for friction factor?

Yes. Evaluate the left and right sides in separate cells, compute their difference in a residual cell, then run Goal Seek driving the residual to zero by changing the friction-factor cell. With Re = 100,000 and ε/D = 0.0001, the solution is f ≈ 0.0185.

Why does Excel say Goal Seek may not have found a solution?

The search usually hit its iteration limits or struggled from the starting value, or the function is discontinuous or non-monotonic near the guess. Try a better initial guess, split the calculation into intermediate cells, or adjust the iteration settings under File → Options → Formulas.

When should I use Solver instead of Goal Seek?

Use Solver when the problem involves more than one changing cell, requires constraints, or calls for optimization rather than matching a single target value; Goal Seek adjusts exactly one input against one target.

References

  1. Charlie Young, P.E., 'Solving Non-Linear Equations in Excel with Goal Seek,' EngineerExcel. https://engineerexcel.com/solving-non-linear-equations-in-excel-with-goal-seek/
  2. Aubrey Kagan, 'Use Excel Goal Seek to Solve Design Equations,' EDN, June 21, 2013. https://www.edn.com/use-excel-goal-seek-to-solve-design-equations/
  3. Mohamed Musaddag El-Awad, 'Use of Excel's Goal Seek feature for Thermal-Fluid Calculations,' Spreadsheets in Education (eJSiE), Vol. 9, Issue 1, Article 3, 2016. https://sie.scholasticahq.com/article/4657-use-of-excel-s-goal-seek-feature-for-thermal-fluid-calculations/attachment/13949.pdf
  4. Harlan H. Bengtson, PE, PhD, 'Pipe Flow/Friction Factor Calculations using Excel Spreadsheets,' McGraw-Hill Education, June 2015. https://info.mheducation.com/rs/128-SJW-347/images/Pipe-Flow-Friction-Factor-Calculations-with-Excel-Spreadsheets-3-June-15-final.pdf

Related Terms

Leave a Reply

Your email address will not be published. Required fields are marked *