Sum vs. Addition

[davegb]

I have some data spreadsheets that come from the statistical program
SPSS. My job is to convert them into decent looking, readable,
understandable to our non-mathematical staff data. With a lot of help
here, I've been doing that.

The latest batch came in and when I started adding data from different
columns to create the "consumer" product, I got #VALUE errors in in the
totals where I was adding in blank cells. I cleaned up the spreadsheet
using the Clean and RemoveSpaces macros, but it didn't help this time.
I replaced the additions with SUM formulas adding up the same cells,
and, voila! They work fine.

I've used adding cells many times with blank cells included and never
had a problem. So why this time? There must be something still resident
in those "blank" cells from SPSS that XL doesn't like, but what? Anyone
else ever have a similar experience? Any suggestions as to how to fix
it?

Thanks!

 

[Guest]

The SUM() function has been designed to ignore text within the summed range.
=A1+B1+C1 will complain.



To cleanup any cells that should be empty, but contain "invisible" junk,
select them and pull-down:

Edit > Clear > All

 

[Guest]

A blank cell is OK with addition, but a non-numeric cell in the value is not.

next to an offending cell (Assume the offending cell is A1) put

=Len(A1)

If it show zero, then more than likely you have a null character in the
cell. You can produce such a character by putting in the formula

=""
then select the character and do
Edit=>copy
then Edit=>Pastespecial and select values.

If the length is not zero, then you obviously have some non-numeric value in
the cell.

for the null characters, you can select the range and do
Edit=>Replace
leave the replace What blank
put in $$$$ in the Replace With

no repeat this, but put
$$$$ in replace what and leave Replace with Blank.

Thanks to Dave Peterson for this hint.

 

[Guest]

And since SUM(), COUNT(), etc ignore text, it is always a good idea to include
=COUNT(dataRange)
with imported data, just to be certain that your calculations are actually
using all the data that you think they should be using.

Jerry

 

[davegb]

A blank cell is OK with addition, but a non-numeric cell in the value is not.

next to an offending cell (Assume the offending cell is A1) put

=Len(A1)

If it show zero, then more than likely you have a null character in the
cell. You can produce such a character by putting in the formula

=""
then select the character and do
Edit=>copy
then Edit=>Pastespecial and select values.

If the length is not zero, then you obviously have some non-numeric value in
the cell.

for the null characters, you can select the range and do
Edit=>Replace
leave the replace What blank
put in $$$$ in the Replace With

no repeat this, but put
$$$$ in replace what and leave Replace with Blank.

Thanks to Dave Peterson for this hint.

Thanks for all the replies! Good stuff to know.