A1 is seen as text, it can be coerced by calculating thus the A1+B1 will
work but =SUM(A1:B1) ignores text.
Copy an empty cell, select A1, do editpaste special and select add, now the
SUM should work


--

Regards,

Peo Sjoblom


"Russ" wrote in message
m...
I am seeing some strange behavior from the SUM() and AVERAGE()
functions. To most efficiently convey the problem here is a summary of
the calculations I've tried within excel:

Cell A1: 24
Cell B1: 6

SUM(A1:B1) 6
SUM(A1,B1) 6
A1+B1 30

AVERAGE(A1:B1) 6
(A1+B1)/2 15

SIN(A1) -0.905578362
SIN(24) -0.905578362

A1 / 2 12
A1 + 2 26

So everything works fine for arithmetic operations, and even a SIN()
function but the SUM() and AVERAGE() functions are not providing
accurate results.

I can cut/paste cells into a clean sheet or 'paste special.' as values
and get the same behavior. I've also tried changing everything to
general format with no effect.

If I just retype 24 into the A1 suddenly everything works fine. The
obvious answer is that cell A1 is a text value but the question is how
did it get that way? I manually typed 10 rows of data each with 25
columns and when I went through and did a check there were about 10
values at various locations exhibiting this behavior. These values
were all in the different rows/columns and everything was in general
format.

Also, if it was actually a text value, why does the SIN() function and
simple arithmetic operations work but the summation and average do
not?

This is quite disturbing. I've used these functions in the same way on
countless worksheets! It's scary to thing that this was happening all
along and I just never caught it.

Any insights? Any suggestions as far as checking existing sheets for
cells that are behaving this way?

Thanks,
Russ D.