Iterative calculation to allow circular reference

There is no way I can make sense of that endless chain of references, sorry. I gave up after backtracking over 50 times.

I understand your frustration. I find 150 cells with #NUM errors.

But I find only 6 chains of circ refs, to wit:

D284 > D41 > D40 > D37 > D410 > D396 > D284

D681 > D438 > D437 > D434 > D807 > D797 > D793 > D681

                                                                 v

                                                             D807

C284 > C41 > C40 > C37 > C410 > C396 > C284

C681 > C438 > C437 > C434 > C807 > C797 > C793 > C681

                                                                 v

                                                             C807

FYI, to find these, I open the file in Manual Calculation mode (*), and I disable Iterative Calculation mode.

Then for each chain of circ refs, I click Calculate Sheet, and I use Error Checking > Circ Refs to see each chain.

After I discover each chain, I break it in order to find the next chain.

Breaking a chain simply requires removing the reference to the "next" cell in the chain. For example, in D284, delete the formula =D41.

In D797, I remove "+D807". Similarly in C797.

(*) To open the file in Manual Calculation mode, I start Excel with a new worksheet, set Manual Calculation mode, then open the user's file in the same instance of Excel.

IMHO this is an issue inside the formula model. Most formulas are pretty basic and use +-*/ as operator.

But some uses LN and LN(0) returns the #NUM! error.

And there are a lot of formulas that refers to empty cells, means this cells are evaluated to 0 by the engine.

Anywhere there is the issue.

Open the file, select C41 and press DEL, as your see many errors are gone. Press CTRL+Z to restore the formula and the errors doesn't come back.

The question is where can we break the chain to reset all the errors... I don't know.

Andreas.

Thanks.

There is no way I can make sense of that endless chain of references, sorry. I gave up after backtracking over 50 times.

Hi Hans,

thx a lot for your reply.

Here is the link to Drop Box.

https://www.dropbox.com/scl/fi/qdw8i5qvesk95m1syxlx7/Circular-Reference-with-Iterative-Process.xlsx?rlkey=lptboa6gxwd93wckw7jxgtzaq&dl=0

Tell me if you have received it please.

Best Regards

JF

Could you create a stripped-down copy of the workbook demonstrating the problem (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.