Addition error!?!

[Nikko963]

Okay, so one of the big benefits of using Excel is that you can trust
the math operations, right? Well, believe it or not I am having a
problem with s simple SUM function. This is a little convoluted but I
appreciate you bearing with me while I weave my tale of woe:

Column A contains numbers ranging from 1 to 25 representing the
quantity of product the customer owns. I did a SUM to give me a total
(427).

Column B indicates the version (five possibilities) of the product
owned by the customer. The next 5 columns put the quantity into a
separate column for each of the five versions (so all those customers
with version 5 have their quantity appear in the column reserved for
all version 5 customers, for example). Then, at the bottom of all
these 5 columns, I do a SUM of each version's total..

Now I know how much of what version each customer has and also a total
breakdown of the versions being held by our customers.

On whim (having always trusted Excel before), I did a SUM of the 5
columns holding the version/quantity data. It should have given me 427,
same as the total for Column A. But it didn't! Column A has a total of
427 and a SUM of my five columns gives me 501.

So, I figured my code for placing the quantity in the correct column
must be faulty. To do a quick check, I inserted a column next to column
A and did a SUM of the five columns holding the version/quantity data.
This SHOULD give me the same number as in column A...or not if had made
a mistake. I figured I would instantly spot my mistake and correct it.

ALL THE NUMBERS MATCHED!!! I couldn't believe it. How could I have
exactly the same numbers but a different total. Just to be sure I did a
SUM down at the bottom of this new column. The total was 501.

There is no difference between the two sets of numbers (the values
anyway) but SUM produces two different totals.

I am bewildered and concerned that I can no longer trust Excel.

Help me, please!!!

 

[JE McGimpsey]

I am bewildered and concerned that I can no longer trust Excel.

You can trust XL - SUM()'s pretty stable and accurate.

The most likely problem is that some of your column A values were
entered as Text (this happens most often when values are pasted in from
another application, but also occurs when cells are preformatted as
Text). Text is ignored by SUM().

To find out, you can try putting this in an unused column (say, column
F):

F1: =ISTEXT(A1)

and copy down. If any results are TRUE, you've found "text numbers".

Make sure column A is formatted with a number format. You can coerce
text numbers to real numbers by copying a blank cell, selecting column A
and choosing Edit/Paste Special, selecting the Values and Add radio
buttons.

FWIW, XL04 has "smart buttons" that pop up when numbers are stored as
Text, to flag this problem.