Another Simple Formula Question
Posted by Eric on October 23, 2001 1:00 PM
How come the following formula always adds up to
"zero" no matter what numbers are entered into the
target cells? (I have tried making the target cells
both text and general cells)
=SUM(E32:F56,N32:Q56)
Eric
Posted by EDDIE G on October 23, 2001 1:03 PM
It works fine for me with general cell format.
Posted by Aladin Akyurek on October 23, 2001 1:07 PM
Eric,
SUM ignores text. It seems the ranges are all text-formatted, otherwise you shouldn' t get 0.
We can test this with:
=SUMPRODUCT((E32:F56+0)+(N32:Q56+0))
Please report back what you get. And, this is important, I'm not suggesting using SUMPRODUCT instead of SUM. Just trying to assess the problem that you have.
Aladin
Posted by Mark W. on October 23, 2001 1:17 PM
Changing the cell format of a text value doesn't change its data type!
A reliable, fast way to change the data type of
a text value after it's format has been changed
to an appropriate number format is to use the
Data | Text to Columns... menu command.
At Step 1 fo 3 of this command's wizard just press
[ Finish ] and text values will become numeric.
Posted by Juan Pablo on October 23, 2001 1:23 PM
Lots of similar problems today.... right Mark ? (NT)
Posted by Mark W. on October 23, 2001 1:31 PM
Yep...
Data type is a difficult concept to grasp because
it's difficult to visualize. It looks like a number;
therefore, it must be! Furthermore, formatting
governs the appearance of a value (text or numeric),
but doesn't change its internal representation. When
someone says I formatted it as "0" they don't realize
they have created a partially qualified number format.
All number formats consist of 4 parts separated by
semicolons.... positive;negative;zero;text. So "0" handles
the first 3 (by default) and says, "if I'm text just show
me as is". If the format 0;-0;0;"text" was used they'd
quickly recognize what they're dealing with. : A reliable, fast way to change the data type of
