My question is pretty easy. I am trying to add multiple columns
together using the following formula: SUM=(A1:A12)
I keep getting an incorrect response from Excel. Any thoughts?
It would be helpful if you were more specific about what "incorrect
response" means, and if you'd mention your operating system and XL
version...
First, any "number" that is entered as Text will be ignored by
SUM(). To coerce text numbers to real numbers:
Format A1:A12 as General or another number format. Select an empty
cell. Copy it. Select A1:A12. Choose Edit/Paste Special, selecting
the Values and Add radio buttons.
Second, if your values are calculated, your displayed values may not
be the values that XL uses to calculate the SUM. For instance, if
A1:A4 are formatted with two decimal places:
A1: =1/3 =0.333333333333333 ----displays as---> 0.33
A2: =1/3 =0.333333333333333 ----displays as---> 0.33
A3: =1/3 =0.333333333333333 ----displays as---> 0.33
A4: =SUM(A1:A3) = 1.00000000000000 ----displays as---> 1.00
which looks as if SUM() is adding incorrectly. To fix, either choose
Preferences/Calculation/precision as displayed (which is a global
setting, so may change other items on your sheet), or use ROUND() to
convert the values:
A1: =ROUND(1/3,2) =0.330000000000000 ----displays as---> 0.33
A2: =ROUND(1/3,2) =0.330000000000000 ----displays as---> 0.33
A3: =ROUND(1/3,2) =0.330000000000000 ----displays as---> 0.33
A4: =SUM(A1:A3) =0.990000000000000 ----displays as---> 0.99
Third, binary values, like decimal values, can't always be
represented exactly in a finite number of digits. Therefore there
could be rounding errors. XL maintains approximately 15 decimal
digits of precision, and uses IEEE floating point arithmetic to
reduce the rounding errors, but like all spreadsheets, they still
exist. See
http://cpearson.com/excel/rounding.htm for a full explanation.
If the above don't cover what you're seeing, post back with details.
