Software

Get Excel to calculate formulas with circular references

Don't go around in circles unnecessarily—when you have a circular reference in Excel, the program usually gives you an error message. However, if you want to calculate repeatable formulas, you can get Excel to do circular calculations on your terms. Mary Ann Richardson tells you how.

In Excel, formulas that have circular references usually do not run and come back with errors. A circular reference error results when a formula refers back to its own value. For example, when the formula =B1+B3 is entered into B3, it creates a circular reference; the formula in B3 repeatedly recalculates because, each time it is calculated, B3 has changed.

There are certain formulas that must calculate repeatedly and will require a circular reference to achieve a correct result. For example, suppose you plan to open an investment account with $100, and add $100 to it each year for 10 years. You decide to set up a formula that will calculate what you will have saved over the 10-year period. To force the calculation of this repeatable formula, follow these steps:

  1. In A1, enter Current Investment.
  2. In A2, enter =B2.
  3. In B1, enter Total Investment.
  4. In B2, enter =100 + A2.
  5. Click the Cancel button.
  6. Go to Tools | Options and click the Calculation tab.
  7. Click the Iteration check box.
  8. Enter 10 in the Maximum Iterations text box.
  9. Click OK.

By setting the maximum number of iterations for the formula, Excel will ignore the circular reference and calculate the formula 10 times. Thus, to have Excel let you use circular references in repeated iterations of the same formula, you simply need to tell Excel the number of times to perform the calculation.

Miss a tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

4 comments
Den D
Den D

The example works as described. Thanks for the 'tip' which is useful [to me] for modeling exponential average response (damped) performance to 'noisy' real world stimulus.


nareshhusys
nareshhusys

how can we know that excel calculates correctly.. as when i am using in a relatively complex formula... I see that for the same input .. the calcualtions are different.. how to address that...

Tony Hopkinson
Tony Hopkinson

That's recursion with no exit condition, a well known programming error.

Editor's Picks