Excell mistake in addition

  • Thread starter Thread starter mlv
  • Start date Start date
M

mlv

When adding a column of numbers, the sum function failed to accurately sum
the numbers. Even summing just 1200, 800, 700, 700 yielded 1900. How do I
correct this internal error?
 
Hi

1200 and one of 700 are numbers, another 700 and 800 are texts.

1.Format the range with your numbers as numeric or general;
2. Into some empty unformatted cell somewhere enter a number 1, and copy the
cell;
3. Select the range with your numbers, and Paste Special as Multiply - now
your formula must return right result;
4. Clear the cell you used to store number 1.


Arvi Laanemets
 
mlv said:
When adding a column of numbers, the sum
function failed to accurately sum the numbers.
Even summing just 1200, 800, 700, 700 yielded
1900. How do I correct this internal error?
Click to expand...

First, it is unlikely an "internal error". It is more likely a user error
-- yours.

Second, if you want us to help you, you need to help us. At a minimum, you
should provide the formulas that fail. Perhaps that is the root cause of
your mistake.

Another common mistake: some of the "numbers" are actually formatted as
Text. In that case, SUM(A1:A4) will ignore the those "numbers".

Ironically, =A1+A2+A4+A4 will interpret the text as numbers. But I am not
suggesting that you replace SUM with addition (+) for the purpose. It
probably would be better to correct the cell format (then press F2, then
Enter).

But if using addition instead of SUM does not work around the problem, that
might imply an additional common mistake with "numbers" that are text: some
of the "numbers" are surrounded by nonbreaking space characters -- CHAR(160).
This is not uncommon when the data is captured from web pages.

In that case, before correcting the cell format, you need to remove the NBSP
characters. For example, put the following formula into another cell:
=SUBSTITUTE(A1,CHAR(160),""); then copy-and-paste-special-value that cell
into original cell (A1).

Those are wild-ass guesses, based on the lack of information from you. If I
didn't get lucky ;-), more information from you will probably result in
better responses.
 
Back
Top