Solving Nonlinear Equations
with Spreadsheets

DOFPro Team

Some Nonlinear Equations in Chemical Engineering

The Antoine equation

\[\log_{10} p^* = A - \dfrac{B}{T+C}\]

The Arrhenius equation

\[k = k_0 \exp(-E_a / RT)\]

The Benedict-Webb-Rubin equation \[P = \frac{RT}{\hat{V}} + \left( B_0 RT - A_0 - \frac{C_0}{T^2} \right) \frac{1}{\hat{V}^2} + \left( bRT - a \right) \frac{1}{\hat{V}^3}\] \[+ \frac{\alpha a}{\hat{V}^6} + \frac{c}{\hat{V}^3 T^2} \left( 1 + \frac{\gamma}{\hat{V}^2} \right) \exp \left( -\frac{\gamma}{\hat{V}^2} \right)\]

How Do You Solve Them on a Spreadsheet?

  • Set up as \(f(x) = 0\).

  • Guess and Check
        Root is between sign changes

  • Graph it and Guess and Check

  • Goal Seek

Set Up as \(\mathbf{\mathit{f}(\mathit{x}) = 0}\)

The Antoine equation \[f(p^*)\text{ or }f(T) = \log_{10} (p^*) - A + \dfrac{B}{T+C}\]

The Arrhenius equation

\[f(T) = k - k_0 \exp(-E_a / RT)\]

The Benedict-Webb-Rubin equation

\[f(\hat{V}) = P - \left[\frac{RT}{\hat{V}} + \left( B_0 RT - A_0 - \frac{C_0}{T^2} \right) \frac{1}{\hat{V}^2}\right. \] \[+ \left. \left( bRT - a \right) \frac{1}{\hat{V}^3} + \frac{\alpha a}{\hat{V}^6} + \frac{c}{\hat{V}^3 T^2} \left( 1 + \frac{\gamma}{\hat{V}^2} \right) \exp \left( -\frac{\gamma}{\hat{V}^2} \right) \right]\]

Technique Demos

Goal Seek Suggestions

  • Increase accuracy of \(x\) in \(f(x)=0\) by multiplying \(f(x)\) by \(10^8\).

  • Mark or label cell to set to 0 (the \(f(x)\) value).

  • Mark or label cell to adjust (the \(x\) value).

Some Final Notes

  • Goal seek works fine on linear functions.
  • It is limited to functions of one variable.
  • If you need to find the roots of a multivariable function, \(f(x, y) = 0\), you can use Solver instead of Goal Seek.
  • If you need to find the roots of two simultaneous nonlinear functions, you can iterate by using Goal Seek on one variable and then on the other, and go back and forth until you find both roots, but at that point you’re probably better off using Python or R or some other programming language.

The Takeaways

  1. You can find the roots of a nonlinear equation on a spreadsheet with guess-and-check, graphically, or with Goal Seek. If you’re close enough to a root, Goal Seek is fastest, easiest, and most accurate.
  2. Rewrite your function as \(f(x) = 0\).
  3. Multiply your \(f(x)\) cell by \(10^8\) to increase the sig figs in your x-value.
  4. Label your \(x\) and \(f(x)\) cells so you can find them quickly.






Thanks for watching!
The previous video in the series is in the link in the upper left. The next video in the series is in the links in the upper right. To learn more about Chemical and Thermal Processes, visit the website linked in the description.

The DOFPro Team