Excel

[Cecil_Dunagan]

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?

[#2]

@Cecil_Dunagan 

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)

[#3]

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

[#4]

@Cecil_Dunagan 

Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.

[#5]

@Cecil_Dunagan 

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)

[#6]

Its a financial spreadsheet that has worked for several years. Never had text in it, just numbers'

[#7]

Hi Hans,

Can't seem to be able to attach it here. Not sure if that is possible

[#8]

@Cecil_Dunagan 

As I wrote: "if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar"

[#9]

@Cecil_Dunagan 

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.

[#10]

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?

[#11]

@Cecil_Dunagan 

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.

[#12]

Thanks Peter,

Your idea about an inadvertent error in handling the data proved to be the answer. I checked the formulas on a feeder sheet and found one line that was not proper.. Instead of summarizing a month's transactions in each category, it summarized not only the current month but also the remainder of the year which seemed to be in conflict with subsequent months that properly added up the month's transactions in each category. Formula (user) error, to be sure.