Forum Discussion
Excel
If I enter a simple formula such as SUM(A1:A7), it gives me the wrong total and sometimes comes back as 0 when there are values in the listed cells. Doesn't seem to be a erroneous formula, so what could cause this?
11 Replies
Given a sufficient level of malice all sorts of things are possible!
I have used a filled formula to demonstrate that 'what you see is not necessarily what is there'. In day to day life the most common situation is numbers as text.
- Why would it arbitrarily change from numberic to text after reliably working in the same spreadsheet for over 8 years? Are you saying the malice is ion Microsoft's part?
No, not really. These, and a handful of other techniques came from a book which had a section helping auditors look for fraud. In your case it will be some inadvertent error in handling the data. However, the idea of applying a SUM function to each individual value before attempting to sum the range has some merit.
The most probable explanation is that A1:A7 contain text values (that may look like numbers).
You can try converting the values to 'real' numbers, or change the formula to
=SUM(--A1:A7)
- Neither work. there are no text values and reformatting them didn't help. Your formula tweak with the two minus signs didn't work either
How do you know is that text or not? Re-formatting doesn't convert texts to numbers and the opposite, at least you shall re-enter values after applying another format.
To check you may enter in any empty cell =ISTEXT(A1) or so.
IF =SUM(--A1:A7) doesn't work, perhaps =SUMPRODUCT(--A1:A7) or =SUMPRODUCT(A1:A7*1)
