Baffling Sum/Average Behavior

[Russ]

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.

 

[Peo Sjoblom]

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 edit>paste special and select add, now the
SUM should work

 

[Ron Rosenfeld]

You are correct in that the misbehaving cell is a TEXT value. SUM and AVERAGE
purposely ignore text values; other Excel operators try to convert them.

I don't know how they got that way. Perhaps in your typing numbers in manually
you accidentally hit an apostrophe; or your keyboard is misbehaving.

To correct things manually, you could select a blank cell and Edit/Copy.
Then select the area on your worksheet where the data exists.
Then Paste Special/Operation Add.

To detect errant cells, select the data area; assume it is A1:N10.

Format/Conditional Formatting Formula Is: =ISTEXT(A1)
Format to taste and the errant cells should "light up"

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.

--ron