Excel not adding properly

Excel not adding properly- nonsensical results (that change as well)

Excel not adding properly and giving a nonsensical result? Something like

10 + 10 = 0 (and it gives you a warning message) or

10 + 10 = 2 000 (with no warning messages).

This is the result of a circular reference in the spreadsheet. As an example open a spreadsheet and in cell

  • A1 type 10,
  • in A2 type 10 and
  • in A3 type =sum(A1:A3) and press enter.

You will either get a 0 (with an error message) or a number bigger than what we expect.

The reason is that in the SUM calculation we have (accidentally!) added the result cell itself (so in cell A3 we want to sum up everything, including A3).

So Excel adds 10 + 10 and remembers 20 in A3 but then needs to add 20 (as it is part of the SUM). It now has a 40 in cell A3 and if it adds 10+10+40 then the number is 60. But 60 now needs to be added to the 10 + 10 and so it goes on until Excel stops.

Circular referencing is a common problem and often happens as a mistake. In the case above, it could simply be a case of dragging the sum box one cell to far.

They are notoriously difficult to find and unlike the situation above which is accidental, it can show an error in logic in a more complex model. Occasionally circular referencing is used purposefully especially with interest calculation but we strongly advise against this unless you are a very experienced modeller.

We suggest that you always have your Excel Options set so that it doesn’t allow circular referencing ( Click FILE, OPTIONS, FORMULAS and make sure your Enable Iterations is NOT TICKED). This way if you accidentally create a circular reference you will be warned. Intermediately re-look at what ever you were doing with the last formula and correct it.