Microsoft Office Tutorials and References
In Depth Information
Circular Reference Examples
Solving a recursive equation
A recursive equation is an equation in which a variable appears on both sides of the equal sign.
The following equations are examples of recursive equations:
x = 1/(x+1)
x = COS(x)
x = SQRT(X+5)
x = 2^(1/x)
x = 5 + (1/x)
You can solve a recursive equation by using a circular reference. First, make sure that you turn on
the Enable Iterative Calculation setting. Then convert the equation into a self-referencing
formula. To solve the first equation, enter the following formula into cell A1:
The formula converges at 0.618033989, which is the value of x that satisfies the equation.
Sometimes, this technique doesn’t work. For example, the formula allows the possibility of a
division by zero error. The solution is to check for an error. If the formula displays an error, modify
the iterated value slightly. For example, the preceding formula can be rewritten using the
IFERROR was introduced in Excel 2007. Following is a version of the formula that’s compatible
with previous versions of Excel:
Figure 16-6 shows a worksheet that calculates several recursive equations in column B. The
formulas in column D provide a check of the results. For example, the formula in column D2 is
Formulas in column E display the difference between the values in column B and column D. If the
solution is correct, column E displays a zero (or a value very close to zero).
You can access recursive equations.xlsx , the workbook shown in Figure 16-6, on
the companion CD-ROM.