Lunchtime learning - Excel

Search other Excel resources (AccountingWeb, Beancounters Guide, IT Counts):

Avoiding circular references

Iteration

A circular reference occurs when the formula in a cell refers to the same cell. Perhaps the most common situation in practice is when calculating the closing bank balance in a cash flow and wanting to base the calculation of interest on the average balance during the year. This would probably involve adding the opening and closing balances and dividing by 2. However, the closing balance will include the interest that you are using the closing balance to calculate. Hence the circular reference. In this example, cell D8 includes a reference to D6 which itself contains a reference to D8:

When we create the circular reference, Excel shows us the following warning screen

If we choose to 'Cancel', and therefore accept the circular reference, Excel will display a warning in the status bar, and fail to try and calculate the reference:

One 'solution' is to 'Enable iterative calculation' via Tools, Options, Calculation tab (Excel 2007: Office button, Excel Options, Formulas, Calculation options):

This enables you to turn on iteration – which means that Excel will keep calculating until either a maximum number of iterations is reached or the change is below the maximum.

Finding a circular reference

Generally it's a good idea to avoid circular references, not least because allowing one intentional circular reference can mask other, unintentional, such references. In a workbook with many sheets, if you are looking at a sheet other than the one containing the circular reference, then the status bar will just display 'Circular references'. To find the offending cell or cells, display the circular references toolbar via View, Toolbars, Circular Reference if it is not already visible (Excel 2007: Formulas ribbon tab, Formula Auditing section, Error Checking drop down, Circular References). The drop down for circular references should show details of the offending cell together with the sheet it is on:

Alternative to a circular reference

Often you can avoid a circular reference entirely by a slight change to the design of your spreadsheet – maybe by adding additional rows or columns to perform intermediate calculations or by working out an alternative formula.

Our interest example is a good one. If you work out the formula for interest algebraically (mathematically capable family members or colleagues could be useful here!) then the circular reference becomes unnecessary:

For a fuller explanation of the maths involved, there is a very useful worked example on handling Circular References on Navigator Project Finance’s webpage.