Calculations adding Text formats

[Barb]

I am new at this so please bear with me.

I am using Excel XP. In my column I have a list of
numbers. Some are formatted as text and some are
formatted at numeric.

When I insert by formula it seems to be calculating the
entire column, even the text formatted cells.

What am I doing wrong.

Example

Cell A1 = 5 - numeric
Cell A2 = 5 - text format
Cell A3 = 5 - text format
Cell A4 = 5 - numeric format

formula states =sum(a1:a4) the total should be 10 not 20.
Correct?

 

[Tod]

It depends on when you do the formatting. If you enter a
number and then reformat the cell to text, it will still
show up in your sum. But if you format a blank cell as
text and then enter the number, it will not be included.

To better insure that the number is not included, put an '
before the number. For example:

5
5
5
5
20 <--sum

5
'5
5
5
15 <--sum

Both sets will show the same way (the apostrophe does not
show), but the number with the apostrophe is treated as
text and is not summed.

tod