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!

[Gary''s Student]

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
--
Gary's Student


"davegb" wrote:

> 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!
>
>

[Tom Ogilvy]

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.

--
Regards,
Tom Ogilvy

and

"davegb" wrote:

> 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!
>
>

[Jerry W. Lewis]

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

"Gary''s Student" wrote:

> 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
> --
> Gary's Student
>
>
> "davegb" wrote:
>
> > 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!
> >
> >

[davegb]

Tom Ogilvy wrote:
> 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.
>
> --
> Regards,
> Tom Ogilvy
>

Thanks for all the replies! Good stuff to know.

> and
>
> "davegb" wrote:
>
> > 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!
> >
> >