CIRCULAR REFERNCE IN EXCEL

Get Excel to calculate formulas with circular references
Takeaway: 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.