excel - How to fix Circular Reference Dilemma in Excel

Question

I got three number values for which all the three are interconnected. So on using formulas to interconnect all the three I am having this circular reference error. Although the formulas work out in giving interconnected answer, on using other formulas over these values, lets say doing autosum on one of those three columns, it returns wrong answer. The situation could only be sited better with an example.

In this sample situation, values in columns B, C and D are interconnected.

B=D/C, C=D/B, D=B*C,

These three values are interconnected such that when any two values are entered the third one is automatically calculated. Enter the PRICE and TOTAL, then the QTY is automatically calculated; enter TOTAL and QTY, then PRICE is automatically calculated; enter PRICE and QTY, TOTAL is automatically calculated. And doing this formula gives the CIRCULAR REFERENCE ERROR. Although by clicking OK to the error warning, the values still get calculated as per the requirement, but there is this disturbing error message always in the sheet, and also additional formulas to any of those values won't work. In this given sample, the value of D13 returns wrong answer.

It would be so much useful if I could get a help here. Thanks in advance.

标签： excelexcel-formulaspreadsheet

Answer 1

I'm pretty sure that circular references are the wrong approach for whatever you are trying to do, but try with an array formula like =SUM(IFERROR(B4:11;0)). Because it's an array formula, it must be entered pressing CTRL+ENTER+SHIFT

This formula will ignore all the error values, and sum up the right ones.

I would strongly suggest a different aproach for whatever you are trying to do.

SUGGESTION: Duplicate columns. Make 3 columns that will have, at least, 2 inputs. Then in the duplicate of those columns, put formulas with your rules:

My formulas are only in the orange part (yellow zone is for typing 2 or 3 values only)

1. E4: =SI(B4="";G4/F4;B4)
2. F4: =SI(C4="";G4/E4;C4)
3. G4: =SI(D4="";E4*F4;D4)

All of them check if the input is blank or not. If blank, then make calculation. If not, then gets the value of the original input.

Notice in yellow zone that in each row I got 2 different inputs. Orange zone gets calculated properly, with no circular references, and I can sum up all of them.

This will work only if you always type 2 inputs in the yello part. If you type just 1, it will raise circular reference error.