An in-depth analysis of Excel circular references

This article brings you relevant knowledge about excel, which mainly introduces related issues about circular references. Circular references are a term in worksheet functions, which refer to direct or It indirectly references the value of the cell where the formula is located. Let's take a look at it. I hope it will be helpful to everyone.

Related learning recommendations: excel tutorial

When the workbook is opened, Excel pops up an information prompt box saying that One or more circular references exist, which may result in incorrect calculation results - what happens?

First of all, it should be noted that the "circular reference" in the pop-up window is a term in the worksheet function, which refers to the direct or indirect reference to the formula. The value of the cell.

Let me give two simple examples.

Enter the formula =A2 1 in cell A2. It is necessary to return the calculation result of the formula in cell A2, but also to add 1 to the value of cell A2, repeating the cycle, and the system will be confused.

Or enter the formula in cell K8:

=I8 J8

Then enter the formula in cell J8:

=ROUND(K8-H8-I8,2)

Although the formula of J8 does not directly calculate the value of its own cell, it calculates I8, and I8 calculates J8. This If J8 indirectly refers to itself, it will also produce a circular reference.

When you enter a circularly referenced formula for the first time in Excel, the system will pop up a prompt message as shown above, asking you if you have any ideas.

But after you close the prompt message box and write or calculate a formula containing a circular reference again, the system will ignore you and silently return the calculation result of the circular reference to 0.

Snap your fingers, the above are the causes and consequences of circular references.

So, if there is already a circular reference in Excel, or you see a message box prompting a circular reference, how should you crack it?

Step 1, first find the cells with circular references.

Under the Formula tab, click the [Error Check] → [Circular Reference] command in sequence. You can see an option list that displays the cell addresses with circular references.

In the option list, select any address, and the system will automatically jump to the relevant cell.

Step 2, rewrite the formula.

Delete old formulas with circular references, reorganize the calculation logic, and write new formulas.

Related learning recommendations: excel tutorial

The above is the detailed content of An in-depth analysis of Excel circular references. For more information, please follow other related articles on the PHP Chinese website!