Forum Discussion
Calculation using the IF function
I made the https://tuiasiro-my.sharepoint.com/:x:/g/personal/marius-stelian_imbrea_staff_tuiasi_ro/EcSuAMfDbrpMqPhgKrNi8ncBf6CwTwOLxdl2XFg3FObQ_g?e=Gk9ZSN 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?
3 Replies
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.
- Thank you
I understand the problem now
The proposed function does not solve the situation but it helped me to simulate and understand where the problem occurred.Yes, I didn't propose that as a formula to work, only to illustrate why an issue appeared.
