Circular Reference Examples | Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

Following are a few more examples of using intentional circular references. They demonstrate creating circular references for entering unique random numbers, solving a recursive equation, solving simultaneous equations, and animating a chart.

Note

For these examples to work properly, the Enable Iterative Calculation setting must be in effect. Choose Excel Options, navigate to the Formulas section, and mark the Enable Iterative Calculation check box.

Generating Unique Random Integers

This example demonstrates how to take advantage of a circular reference to generate unique (nonduplicated) random integers in a range. The worksheet in Figure 16-5 generates 15 random integers between the values specified in the Lowest (E1) and Highest (E2) cells. A.

image from book
Figure 16-5: Using circular reference formulas to generate unique random integers in column A.

Column B contains formulas that count the number of times a particular number appears in the range A1:A15 (named RandomNumbers). For example, the formula in cell B1 follows. This formula displays the number of times the value in cell A1 appears in the RandomNumbers range:

 =COUNTIF(RandomNumbers,A1)

Cell B17, named Dupes, displays the number of duplicated values using this formula:

 =SUM(B1:B15)-COUNTA(B1:B15)

Each formula in column A contains a circular reference. The formula in cell A1, which was copied down the column, is

 =IF(OR(Dupes<>0,(AND(A1>=Lowest,A1<=Highest))), RANDBETWEEN(Lowest,Highest),A1)

The formula examines the value of the Dupes cell; if this value does not equal 0-or, if the value in the cell is not between Lowest and Highest-a new random integer generates. When Dupes equals zero, all cells in the RandomNumbers range are different, and they are all within the specified value range.

Cell D17, which follows, contains a formula that displays the status. If the Dupes cell is not 0, the formula displays the text CALC AGAIN (press F9 to perform more iterations). When the Dupes cell is zero, the formula displays SOLUTION FOUND.

 =IF(Dupes<>0,"CALC AGAIN","SOLUTION FOUND")

To generate a new set of random integers, press F9. The number of calculations required depends on

The Maximum Iterations setting in the Formulas section of the Excel Options dialog box. If you specify a higher number of iterations, you have a better chance of finding unique values.
The number of possible values (specified in the Lowest and Highest cells). Fewer calculations are required if, for example, you request the 15 unique values from a pool of 100.

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 selfreferencing 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 Excel 2007's new IFERROR function:

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

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)

image from book
Figure 16-6: This workbook uses circular references to calculate several recursive equations.

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).

On the CD

You can access recursive equations.xlsx, the workbook shown in Figure 16-6, on the companion CD-ROM.

Solving Simultaneous Equations Using a Circular Reference

In some cases, you can use circular references to solve simultaneous equations. Consider the two simultaneous equations listed here:

 3x + 4y = 8 3x + 8y = 20

You need to find the value of x and the value of y that satisfies both equations. First, rewrite the equations to express them in terms of x and y. The following represents the first equation, expressed in terms of x:

 x = (8 - 4y)/3

The following equation represents the second equation, expressed in terms of y:

 y = (20 - 3x)/8

As shown in Figure 16-7, cell B5 is named X, and cell B6 is named Y. The formulas in these cells mirror the previous equations. The formula in B5 (X) is

 =(8-(4*Y))/3

image from book
Figure 16-7: This worksheet solves two simultaneous equations.

The formula is cell B6 (Y) is

 =(20-(3*X))/8

The figure also shows a chart that plots the two equations. The intersection of the two lines represents the values of X and Y that solve the equations.

Note the circular reference. The X cell refers to the Y cell, and the Y cell refers to the X cell. These cells converge to display the solution:

 X = -1.333 Y = 3.000

Using intentional circular references to solve simultaneous equations is more of an interesting demonstration than a practical approach. You'll find that some iterative calculations never converge. In other words, successive recalculations will never hone in on a solution. For example, consider the simultaneous equations that follow. A solution does indeed exist, but you cannot use circular references to find it.

 x = 4 - y/2 y = 3 + 2x

Cross Ref

Using matrices is a better approach for solving simultaneous equations with Excel. See Chapter 10 for examples.

On the CD

The companion CD-ROM contains the workbook simultaneous equations.xlsx with two sets of simultaneous equations. You can solve one set by using intentional circular references; you cannot solve the other set using this technique.

More about Circular References

For a practical, real-life demonstration of a circular reference, refer to the sidebar, "About Circular References," earlier in this chapter.

Animating a Chart Using Iteration

The final intentional circular reference example involves a chart (see Figure 16-8). It's certainly not a practical example, but it may help you understand how circular references work.

image from book
Figure 16-8: This uses a single-point data series that's calculated with a circular reference formulas.

The line series on the chart displays the COS function for values ranging from 0 to approximately 12.6, using the data in A6:B24.

The chart has an additional data series, consisting of a single point, in range A2:B2. This data series displays as a single large round marker on the chart. The circular reference formula in cell A2 is

 =IF(A2>12.6,0,A2+0.005)

The formula in cell B2 is

 =COS(A2)

When you press F9 to calculate the worksheet, the value in A2 increments, thereby changing the position of the round marker on the chart. Press F9 repeatedly and watch the marker move along the line. The amount of marker movement depends on two factors:

The increment value in the formula (set at .005)
The Maximum Iterations setting, in the Formula tab of the Excel Options dialog box

When Maximum Iterations is 100 and the increment is .005, each calculation increases the value in cell A2 by 0.5. The IF function in the formula resets the value to 0 when it exceeds 12.6. Therefore, the marker returns to the left side of the chart and starts over.

On the CD

This example, named iterative chart animation.xlsx, is available on the companion CD-ROM.