Forum Discussion
Formula calculation displayed wrongly (IF results)
Dear community,
I have a problem because first time I see something like this.
Formula IF with two logic AND criteria calculates correctly, but result is showing in Cell as 0 (zero).
In both marked Cells are Formulas like below. The calculation is correctly because Formula result show correct values (upper Cell should be ="", lower Cell should be =2), but as you see both are shown as =0.
Cell formating change is not helping to show the correct value.
Previously I had the same formula but second logic criteria was just checking one, direct, upper Cell and everything was showing correctly, but I need only one value in range Q15:Q35, so I had to change it.
Please help me to uderstand or give me some ideas for investigation.
If you look at the status bar at the bottom, you'll see that the sheet has circular references, i.e. formulas that refer to the cell containing that formula.
This prevents Excel from recalculating formulas correctly.
The circular reference occurs in G5:G14. The formula in G5 is
=IF(AND(E5>=$F$2,SUM(G$4:$G5)=0),MAX($F$5:$F$14),"")
As you can see, the formula refers to G5 itself.
Change the formula to
=IF(AND(E5>=$F$2,SUM(G$4:$G4)=0),MAX($F$5:$F$14),"")
and then fill down to G14.
The circular reference warning disappears, and the formulas return the desired result:
5 Replies
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
I have prepared some example what I did and what I need.
You may check in below linked file that in Cell G9 both logic parameters are TRUE and Formula result is 8 but Cell is showing 0 (zero). If it would show correctly all below should be also FALSE and in result only this one Cell would show 8, what is my goal.
https://www.dropbox.com/scl/fi/68tkmp7z7ximghi5xnc06/IF-formula-problem-sample.xlsx?rlkey=hai17uqa7ljmea9ptjtzxz3ra&st=02igares&dl=0
If you look at the status bar at the bottom, you'll see that the sheet has circular references, i.e. formulas that refer to the cell containing that formula.
This prevents Excel from recalculating formulas correctly.
The circular reference occurs in G5:G14. The formula in G5 is
=IF(AND(E5>=$F$2,SUM(G$4:$G5)=0),MAX($F$5:$F$14),"")
As you can see, the formula refers to G5 itself.
Change the formula to
=IF(AND(E5>=$F$2,SUM(G$4:$G4)=0),MAX($F$5:$F$14),"")
and then fill down to G14.
The circular reference warning disappears, and the formulas return the desired result:
