Share via

Wrong summation in MS Excel

Anonymous
2020-10-15T13:08:03+00:00

I am facing strange problem while adding the values in some cells. The total sum by formula is shown wrong, but when I select the same cells, the total sum in the status bar is correct. More over when I add any value in one of the bland cells, it doesn't affect the total sum too. It has created doubt for me in the accuracy of the whole Excel file of mine. I've attached the screenshots too. All cells from CY8--CY15 are set to "Accounting", so no cell is set to "text" etc. Cells CY9, CY10, CY12 and CY13 have links with other cells of this sheet.

Hope the question is clear and someone will respond at the earliest.

This problem is existing not only on these cells but in most of the cells of the sheets, so the whole sheet is now unreliable.

For example:

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2020-10-15T15:40:02+00:00

    As shown in the lower left part of the bottom status bar, you have one or more circular references.

    When Excel detects a CR, it aborts the recalculation cycle.  That can cause inconsistencies in cell values.

    Re: All cells from CY8--CY15 are set to "Accounting", so no cell is set to "text"

    First, it appears that CY8 is not formatted as Accounting, based on the far-right-aligned position of 500 in the image.

    Second, having a numeric cell format does not prevent the cell value from being interpreted as text, for any of many reasons.  You need to use ISNUMBER or ISTEXT to be sure.

    That said, the fact that the status bar Sum includes the value of CY8 is probably sufficient to confirm that CY8 is indeed numeric.

    Finally, in the future, it is also important to check (and inform us of) the calculation status, namely Manual v. Automatic.

    1 person found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-10-15T16:28:26+00:00

    Thank Joeu2004.

    I was assuming the same, that may be due to circular reference I am facing this trouble. Though the cells which I mentioned, didn't have circular references and there were some other cells which had circular reference. Moreover there are many circular reference so maybe due to these many circular references, Excel is not behaving normal.

    I'd also tried by switching between "manual" and "automatic" calculation, but it didn't impact the calculation.

    Thanks for helping me out.

    0 comments