Excel nonlinear equations

Use these questions as a learning tool. Treat them like a quiz. Answer in your mind, and using the tool, and writing them down. Make it stick!

• Quiz yourself
• How much can you remember without this list (below)?
• What is missing from this list?
• Don’t just go through the posted notes to answer this. First, recall everything you can, and try it out. Then review notes to recall what you missed.
• Develop the “muscle memory” of using the tool

Linear and nonlinear

• What is the form of a linear equation f(x)?
• What is the definition of a nonlinear equation?
• When writing a nonlinear equation for solution, what form do we put the equation in?
• Linear equations can be solved analytically, but nonlinear equations cannot.
  • Question: what is the approach we use to solve the equation? (Think about the steps before reviewing the solution).
  • Answer:
    • simplify the equation (usually by linearizing it).
    • make a guess of the solution
    • solve the linearized problem
    • use the solution as a new guess
    • repeat (iterate)

Newton’s method

• Write the formula for the Taylor Series for $f(x)$ centered on point $x_0$ including just the first two terms.
• Set $f(x)=0$ and solve for $x$ in terms of $x_0$.
  • This is the equation for Newton iteration. Memorize it.
• How is this translated into Excel?
  • Set a cell for $x_0$ and fill in a guess
  • Set a cell for $x_1$ and fill in the Newton iteration formula
  • Fill the Newton formula down until it converges
• You can split up the parts of the Newton formula so the iteration formula for $x$, $f(x)$, and $f^\prime(x)$ are in separate cells.
  • Then highlight all three and fill down.
    • But make sure the three cells are in the same row or else you can’t do the fill.

Goal Seek

• Works for one equation and one unknown
• Review how this works
• Set a cell for $x$ and a cell for $f(x)$.
  • $x$ contains a value and $f(x)$ is a formula depending on $x$, and optionally other parameters (cells) for your problem, like the gas constant $R$.
• Go to Data tab, then What If Analysis, then Set cell ($f(x)$) to value 0, by changing cell ($x$).

Solver

• Works for one or more equations in one or more unknowns
• Review how this works
• Set cells for unknowns $x_1$, $x_2$, etc.
• Set cells for functions $f_1(x_1, x_2, \ldots)$, $f_2(x_2, x_2, \ldots)$, etc.
• Go to Tools menu –> Solver (may need to include it as an Excel Add In first to see it)
  • Set cell $f_1$ to value of 0, by changing cells $x_1$, $x_2$, etc. (highlight all of them), subject to constraints:
  • Add a constraint for each of the other functions $f_2$, $f_3$, etc. to each also equal zero.
• You probably want to uncheck the box that makes unconstrained values positive.
• Then solve

Alternative method

• You can also solve a system of nonlinear equations by minimizing the sum of the square error of the functions. The function values are errors since the functions should be zero at solution. Squaring the functions makes them positive, and minimizing the sum of the squared functions by changing all variables will drive the sum to zero, which implies all the functions are driven to zero individually. (If you don’t square the function, you could have $f_1=-5$ and $f_2=5$ so $f_1 + f_2=0$, but $f_1\ne 0$ and $f_2\ne 0$.)

Initial guesses

• Nonlinear propblems often have more than one solution
  • Here’s a link to notes from the 541 class that shows a method for finding ALL solutions for a given problem for a single initial guess. It’s called arc-length continuation and is pretty awesome!
• The solution that is found depends on the initial guess
• Also, the problem might not converge at all, which also depends on the initial guess
• Engineering problems often have a physical solution, and other solutions might not be physical
  • Equations that have solutions as negative temperatures, or mole fractions greater than one are examples
  • Sometimes there are more than one physical solution. Stirred flow reactors with heat release (like combustion) have three physical solutions: a “quenched” solution where the output equals the cold input, a reacting solution where the output is the hot products, and an unstable solution in between. Distillation columns can have multiple steady state solutions!
• Choosing a good initial guess is an art. Here are some ideas and guides
  • Choose initial guesses that are physical.
    • If you are solving for the gas temperature, 300 K (ambient) is a better guess than 1 K (nearly absolute zero). The gas property relations are not valid at those low temperature and will cause problems.
    • If you are solving for mole fractions, make initial guesses so that all the mole fractions are between zero and one, and sum to 1.
  • Use simplifying assumptions to find initial guesses.
    • If you are solving properties of a nonideal gas, use the solution of an ideal gas as an initial guess.
    • Assume constant heat capacity, or simplified reactions, or simplified geometry (I have a book titled “Consider a Spherical Cow”).