Simple suming of columns not working

  • Thread starter Thread starter laurel6263
  • Start date Start date
L

laurel6263

When I try to add several columns the result always comes up short.
Example 2731 listed in each of 5 columns [sum(a1,a3,a5,a7,a9) with
total in a11] should total 13655, but excel returns 10924 (2731
short).

I just upgraded to excel 2007 from 2000. I've used this sum function
for years (in excel 2000) and never encountered this problem. I'm
finding the adjustment to excel 07 difficult. I have to learn excel
all over again!!
 
Try to make sure you are adding numbers and not text. That's the usual
cause of summing numbers not appearing to be correct.
 
You say "Example 2731 listed in each of 5 columns..." Do you mean "cells"
instead of "columns"? In what cell is the formula? Exactly what is the
formula? It works for me in 2007. HTH Otto
 
Hi,

Problem:
When numbers are enter as text they may not calculate within formulas as
they should. A few formulas will work fine despite the numbers being entered
as text. Numbers can be stored as text by 1. preformatting the cell to Text
and entering the number, 2. Typing an apostrophy in front of the number '123,
3. Because the data was downloaded from a soure inwhich it was stored as a
number, 4. Because you used the Text to Columns command and converted it to
text., and....

There is no sure indicator that a number is stored as text, although numbers
are usually right aligned and text left aligned, this may not be the case.
If you are using a later version of Excel, Error Checking green triangles may
appear at the top left corner of these cell, but this feature may be off or
the version of Excel may not support it. (2000 and earlier).

You can find out what data type the entries are by using the =ISTEXT(A1) or
=ISNUMBER(A1) functions. You can not tell by checking the Format. If a
number was entered in a cell preformatted as General or as a number, then it
will be a number, even if it's current format is Text. Likewise a number
entered in a cell preformatted as Text will be text even if it's current
format is Number, General, Date, Currency and the like.

Solution:
1. Change the format to one that is numeric and then reenter the numbers
(too slow and error prone.)
2. Select the cells and open the Error Checking options and choose Convert
to Numbers.
3. Select an empty cell and copy it. Select the text number cell and choose
Edit, Paste Special, Add (or Subtract). This method is ~100 times faster
than #2.

Dates are numbers, and if they are stored as text, you will not get an Error
Checking triangle, so method #3 is obligatory if there is a substantial
number of dates to convert.

If this information is helpful, please click the Yes button.

Cheers,
Shane Devenshire
 
There is no sure indicator that a number is stored as text,
Click to expand...

Yes there is! Use ISTEXT or ISNUMBER or TYPE.
Number is 1, text is 2.
Other return values for TYPE can be found in the built-in Excel help.
You can find out what data type the entries are by using the =ISTEXT(A1) or
=ISNUMBER(A1) functions.
Click to expand...

Strange, you contradict yourself.
 
Amedee Van Gasse said:
Yes there is! Use ISTEXT or ISNUMBER or TYPE.
Number is 1, text is 2.
Other return values for TYPE can be found in the built-in Excel help.
Click to expand...


That is not an indicator, that is a method to determine whether it is so.
Shane was saying nothing on the spreadsheet would tell you just by looking
at it.


Strange, you contradict yourself.
Click to expand...


So he didn't in fact.
 
That is not an indicator, that is a method to determine whether it is so.
Shane was saying nothing on the spreadsheet would tell you just by looking
at it.



So he didn't in fact.
Click to expand...

Sorry Bob but that must have been a language issue.
I'm not a native English speaker.
For me, "indicator" and "method to determine" have the same meaning.
 
Back
Top