Solve Equations in Excel

The following tutorials are an introduction to solving linear and nonlinear equations with Excel. The solution to linear equations is through matrix operations while sets of nonlinear equations require a solver to numerically find a solution.

Solve Linear Equations with Excel

Solve Nonlinear Equations with Excel

Linear and nonlinear equations can also be solved with MATLAB and Python. Click on the appropriate link for additional information and source code.

The APMonitor Modeling Language is optimization software for mixed-integer and differential algebraic equations. It is coupled with large-scale solvers for linear, quadratic, nonlinear, and mixed integer programming (LP, QP, NLP, MILP, MINLP). Modes of operation include data reconciliation, real-time optimization, dynamic simulation, and nonlinear predictive control. It is freely available through MATLAB, Python, Julia, or from a web browser interface.

Generative AI Learning

Use these prompts to test your understanding after completing the tutorial. Direct the AI - set up the solutions yourself; use it to check your reasoning.

"Quiz me with 5 questions, one at a time, on solving equations in a spreadsheet: how a linear system A x = b is solved with MINVERSE and MMULT (and why the matrices must be entered as array formulas), the difference between Goal Seek and Solver and when each is the right tool, why a nonlinear equation is arranged into a residual cell f(x) = 0 before solving, why the starting value matters for a nonlinear solve, and how to verify a solution after the solver reports success. Grade my answers and list my misconceptions."
"A cubic equation of state has three real roots at some conditions. Before I try it: ask me to predict what Goal Seek will return if I start the search near each root, and what it will do if I start far from all of them. After I commit to predictions, tell me what actually happens and why a solver only ever finds one root at a time."

Tip: A solver that reports "converged" has answered the question you typed, not necessarily the question you meant. Substituting the answer back into the original equations (a residual check) takes one row of the worksheet and catches most setup errors - make it a habit before you trust any solution, yours or an AI's.

What to Turn In

Submit a short report (PDF, 1-2 pages) with your homework that curates your results into a demonstration of what you learned. You may use Generative AI to help write the report, but you must guide it to the correct solutions, justifications, and assumptions. Answer these questions:

  1. Solve a linear system (3 equations or more) with matrix functions. Show the worksheet and a residual check: A x - b computed in the sheet, with every element near zero.
  2. Solve one nonlinear equation with Goal Seek or Solver. Report the starting value, the solution, and the residual after convergence.
  3. Repeat the nonlinear solve from at least two different starting values. Did you get the same root? Explain the result using the shape of f(x).
  4. From the prediction prompt: what did you predict about starting points and roots, and what did the experiment show?
  5. From the quiz prompt: one question you missed and the corrected answer.

Course Information

Excel and VBA

Python

MATLAB

MathCAD

Related Courses

Admin