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:

=1/(A1+1)

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 function:

=IFERROR(1/(A1+1),A1+0.01)

IFERROR was introduced in Excel 2007. Following is a version of the formula that’s compatible

with previous versions of Excel:

=IF(ISERR(1/(A1+1)),A1+0.01,1/(A1+1))

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

=1/(B2+1)

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.