![Reference Reference](/uploads/1/2/5/4/125428873/117210437.gif)
![Reference Reference](https://www.accountingweb.com/sites/default/files/Circular-Figure4.jpg)
Contents. What is a circular reference? Simply speaking, a circular reference occurs when cell A refers to cell B, and cell B refers to cell A. But often, circular references not only regard two cells, but also follow a long chain of cells. These circular references can slow down the calculation process and more importantly produce wrong results. The basic idea of this method is to avoid circular references in general.
Stop showing a help file for a circular reference warning in Excel with help from a Microsoft certified applications specialist in this free video clip. Existence for both the PC and Mac. The erroneous circular reference appeared in Excel 2008, but not in 2007 when opening the unchanged template. I also have Excel 2007 running in Vista on my Mac. After the updates finish installing, I will try Excel 2007. If I do not place another post, assume that Excel 2007 did not present the same circular reference.
If you want to maintain them, then try to use the so-called iterative calculation. Excel offers two helpful functions concerning circular references. It helps you find circular references, and you can let Excel solve circular references with iterative calculations. Find Circular References To find circular references, follow these steps to identify and remove them. Go to the “Formulas” ribbon. If the “Formula Auditing” group is collapsed, as shown in figure 18, then click on it. Otherwise, just continue to step 3.
Click on “Error Checking.”. If the “Circular References” button is grayed out such that you cannot click on it, then your workbook has no circular references. If it is not grayed out, then your file has circular references. After clicking on the “Circular References” button, you’ll see a list of cells that are involved in the circular reference. This list could potentially contain hundreds of cells. When you enter a formula that turns out to be part of a circular reference, Excel warns you with an error message. It also shows you the circular reference, with blue arrows pointing out the calculation chain for the circular reference.
How to remove circular references People often don’t intentionally create circular references; instead, they usually result from formula errors. To remove unintentionally created circular references, you’ll need to correct the corresponding formulas.
Here’s a simple example. If cell A refers to cell B, and cell B refers to cell A, then you must break the circle, for example by making cell B refer to cell C instead of cell A. Alternatively, you can replace one formula cell of the circle by using hard values.
Copy and paste one of the cells by using the “Paste Special” dialogue (press Ctrl + Alt + V on the keyboard). Select “Values” and press Enter. Enable iterative calculations for solving circular references In some cases, you will want to keep the circular references and allow Excel to iterate in order to calculate the result. Excel 2016 allows up to 32,767 iterations.
To do this, follow these steps. Click on “File” and then “Options.” Navigate to “Formulas,” as shown in figure 19. Check the box “Enable iterative calculation.” You can further set the maximum number of iterations as well as the maximum change at which you want Excel to stop iterating. The default value is 100 iterations. When Excel calculates iterations, you can see the status bar in the bottom-right corner of the screen, which displays the number of the current iteration (for example, “Itr: 1”). In most cases, the maximum number (100 in this case) will not be reached, because the change is smaller than your defined maximum.
In practice, if the maximum number of 100 iterations is reached, then the iteration process is stuck and will not yield better results. You can (carefully) reduce the number and see if the results worsen. But please be careful:. In many cases, allowing iterative calculations leads to wrong results. Iterative calculations can make your workbook very slow. I set up my worksheet where the dates are auto-populated when an initials is entered.
The next day, I put my cursor on the initial field and click on the Save and it automatically change the date to today’s date. As an example I using the following formula: =IF(BK32″”,IF(AND(BN32″”,CELL(“address”)=ADDRESS(ROW(BK32),COLUMN(BK32))),NOW,IF(CELL(“address”)ADDRESS(ROW(BK32),COLUMN(BK32)),BN32,NOW)),””) Also enable the ‘Iterative Calculation’ to automate the date.
Can you please help. Why the date is changing on spreadsheet, when the cursor is left on the initial field after exiting. Please advise.