Michelle said:
We have created a spreadsheet in Excel (2001). The autosum function will
not correctly add the column.
I do not have the "Precision as Displayed" option chosen in the preferences,
and I have tried using "Manual Calculation" along with the "Recalculate
Before Save" option, but it still gives me the same wrong answer.
Can anyone offer some suggestions as to how to get this column to add
properly without having to use a calculator separately??
Excel is almost certainly adding correctly, with the limits of its
designed precision. Therefore the task is to find out why the sum
isn't what you expect.
Autosum isn't really a function, so I'm assuming the function in
your summary cell is something like:
=SUM(A1:A100)
The first thing to check is that the range is correct.
Second, SUM() ignores text. If a cell was formatted as text when an
entry was made, or, sometimes, if the value was pasted in from
another source, the value will look like a number to you, but to XL
it's non-numeric text. One way to coerce text numbers into real
numbers is to copy a blank cell, then select your "numbers" and
choose Edit/Paste Special, selecting the Add and Values radio
buttons.
Since you don't have precision as displayed checked, a third reason
that the sum appears incorrect could be that what is displayed is
different than what is stored in the cell. For instance, with the
display set to 2 digits after the decimal point, 1.234 and 1.233
will both be displayed rounded to 1.23, but their sum (2.467) will
display rounded to 2.47, not 2.46. This is often the problem if the
summed values are the results of calculations.
A fourth reason, especially if you're comparing a sum to another
value, could be the inherent precision of XL. Like most
spreadsheets, XL only keeps 15 decimal digits of precision. And, as
with all spreadsheets, most non-integer numbers cannot be exactly
represented in binary (just like 1/3 cannot be exactly represented
in decimal: 0.3333....). Using IEEE double precision floating point
math, XL usually does a very good job of balancing those errors so
that the result is correct, but sometimes, especially with long
lists of numbers, the small errors add up, and instead of a result
of, say, 2.46000000000000, the result is 2.45999999999999. There are
two ways of compensating for that. One is to round:
ROUND(2.45999999999999, 2) ==> 2.46000000000000
the other is to compare to an arbitrarily small number. If you're
only dealing with 2 digits, say, then you can say that two numbers
within 0.0000000001 of each other are equal:
IF(ABS(2.45999999999999-2.46)<0.0000000001, "Equal", "Different")
will return "Equal".
If none of the above apply to your situation, please give some
actual details, including the type of data, how it's
entered/calculated, and what your expected value is compared to the
returned value.
