Feb 07 2023 06:29 PM
A12 is the sum of Column A. B12 is the sum of Column B. The formula =Sum(A12+B12) returns 0. All cells are formatted as numbers. Can a cell containing a calculation no longer be referenced in the formula of another cell?
Feb 07 2023 06:41 PM
Feb 07 2023 06:53 PM
Feb 07 2023 07:03 PM
Does the attached file work on your system? If it does--which it should--there's nothing wrong with your system. You may have somehow inadvertently set a column to something other than general.
Feel free to post a copy of your spreadsheet on OneDrive or GoogleDrive with a link pasted here that grants edit access to that file. We can't diagnose with just a description that does "It doesn't work."
Feb 07 2023 07:12 PM
Feb 07 2023 07:24 PM - edited Feb 07 2023 09:07 PM
Feb 07 2023 08:09 PM
It is clearly stated in the status bar that there are circular references.
The formula in E12 includes E12 itself. And the formula in D12 includes D12 itself.
Change both formulas and everyone is happy.
Feb 07 2023 08:09 PM - edited Feb 07 2023 08:11 PM
When we download and open the example file, Excel notes a circular reference in E12. There is a similar circular reference in D12. The formulas are:
D12: =SUM(D2:D12)
E12: =SUM(E2:E12)
When there are circular references, Excel aborts the worksheet recalculation. That might leave some cells in inconsistence states.
That is why the calculations in D13 and E13 return zero.
The remedy is to eliminate the circular references. One way:
D12: =SUM(D2:D11)
E12: =SUM(E2:E11)
Alternatively, the following formula allows you to insert rows above the SUM row.
D12: =SUM(D$2:INDEX(D:D,ROW()-1))
Copy D12 into E12
Feb 07 2023 09:05 PM