Excel For Noobs Tutorial Part 62: How to Solve Circular Reference Problems/Formulas Iterations 2016

Microsoft Excel 2016 Tutorial
Microsoft Excel 2013 Tutorial

Excel Circular Reference Formulas How to solce circular reference problems by enabling iterations.
A circular reference error arises when a formula either refers to the cell it is inserted in or refers to another cell that refers to its own cell. For example, if we were to enter =A1+B1+C1 into cell C1 we would get a circular reference warning. This is because we entered a formula into cell C1 that refers to cell C1 as an argument. The problem with formulas that contain circular references is that every time the formula is calculated, it must be calculated again. This could go on infinitely.

When you enter a formula with a circular reference a warning box will appear as shown below.

Circular Reference Excel Formulas 2013

When this box appears you can click OK and Excel will just display a 0 as the result of your formula, or you can click Help and Excel will display a help screen providing more information about circular references.

Circular Reference Help Excel 2013

There are times when circular references are necessary. For example, suppose you owned a company and your employees' salaries were a percentage of net profit. Since net profit is calculated as Revenues -- Expenses and salaries are an expense, we would run into a circular reference problem. We must know what net profits are before we can calculate salaries and we must know salaries before we can calculate net profits. In this case we can use circular references to our advantage. Examine the data and formulas in the Excel screenshot provided below. I am using the show formulas feature so you can see what exactly is entered into the cells. As you can see we have a circular reference problem because Net Profits in cell B4 is =B1-B2-B3 but cell B3 contains a formula that refers to cell B4.

Excel 2013 Formulas circular reference iterations

The formulas entered into this worksheet are resolvable formulas and if the formula is repeated enough times a desired result can be reached for both cell B3 and cell B4. To solve this circular reference problem we simply need to enable iterative calculations. To enable iterative calculations follow the steps below:

Step 1: Select File and then click on Options. The Excel options dialog box will appear.

Step 2: In the Excel options dialog box click the Formulas tab.

Step 3: In the Calculation Options group check the box where it says Enable iterative calculation. Now Excel will run the maximum allowed iterations until it reaches a result where Salaries are exactly 10% of Net Profit. Look at the screenshot below to see the final result.
Microsoft Excel 2016, Excel 2016, Excel 2016 Tutorial, Microsoft Excel 2016 Tutorial

[markhenderson]

Awesome!  This is exactly the problem I was trying to fix!

[Subjectmoney]

Please subscribe, rate and comment. It helps with our ranking.