Forum Discussion
Formula calculation displayed wrongly (IF results)
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:
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:
- It worked. Thank you very much.
I was not expecting that this could be the problem because I get warning only for the circular reference in only one cell and problem was general, but you was right.
Thank you once more.