Excel Scientific and Engineering Cookbook

Chapter 9. Solving Equations

Introduction

In this chapter we’re going to focus on solving equations using Excel. Equation solving, in a general sense, can range from finding roots of single equations, to finding values of independent variables that yield a specific value of a dependent variable, to solving systems of nonlinear equations. There are many traditional hand and computerized methods for solving equations. Newton’s method for iteratively finding roots of nonlinear equations and Gaussian elimination with back substitution for solving linear systems are just a couple of examples of classical equation-solving methods. Hildebrand’s classic book Introduction to Numerical Analysis presents several classical methods for equation solving. The Numerical Recipes books also present algorithms in a variety of programming languages suitable for equation solving.^[*] The methods presented in these and other standard textbooks on numerical methods are just fine and have served scientists and engineers very well. However, in this chapter I want to show you how easy it is to leverage Excel’s built-in features to solve equations with very little programming, and in some cases no programming at all beyond basic spreadsheet formulas. After seeing what Excel has to offer, if you still want to program a classic method, I’ll show you how to use VBA with Excel to implement a few classic techniques.

There’s not much to gain by using Excel or some other tool to solve equations that can readily be ...