Apr 18 2022 01:57 AM
I made the attached table to determine the indexes of some utility meters.
To determine a value for recalculating some consumptions I used the function =IF(ISBLANK(R$3);"";Q4-P4+Q4) which is marked with a light green that works. But I would like to make an improvement and replace the calculation from (value_if_false) with another function that I tried in the table and it is marked in yellow =IF(F4>0;O4-N4+O4;O4-N4+O4-F4+E4, but when I try to combine the two functions =IF(ISBLANK(P$3);"";IF(F4>0;O4-N4+O4;O4-N4+O4-F4+E4)) I don't get the correct result but notification (Microsoft Excel cannot calculate a formula. There is a circular reference in an open workbook, but the references that cause it cannot be listed for you. Try editing the last formula you entered or removing it with the Undo command.)
Can anyone explain to me where the mistake is and how I can correct it?
Apr 18 2022 02:44 AM
With that formula in P4 you reference the cell F4. Formula in F4 in turn reference the range which includes F4. Thus circular reference. If in F4 take shorter range, e.g.
=LOOKUP(9.99E+307,G4:O4)-INDIRECT(ADDRESS(ROW(3:3)+1,MATCH(LOOKUP(9.99E+307,G3:O3),3:3,0)-1,4))
it works.
You need to change the logic to exclude cross-references.
Apr 18 2022 03:22 AM
Apr 18 2022 03:38 AM
Yes, I didn't propose that as a formula to work, only to illustrate why an issue appeared.