up vote 4 down vote favorite
share [g+] share [fb]

I have selected a 5 column by 20 row block in an Excel 2003 SP3 spreadsheet. I have selected Sum from the quick calc options in the status bar. However, the status bar shows a total of £24,000 when it should be ~12,000. The Sum calculation is wrong.

I have done the following to try and diagnose the cause:

  • Reviewed each cell in the selection for a value hidden by colour formatting (none).
  • Tried to Unhide any potentially hidden rows.
  • Reviewed the selection for rogue formulas (none).
  • Copied the data to a new sheet and tried the same Sum. This works correctly!
  • Quick summed each 1*20 column in turn (individually they sum correctly).

Question: What are ALL of the potential causes of such an incorrect calculation?

link|improve this question
1  
What version of MS office are you using – Dheer Mar 22 '11 at 12:31
@Dheer Thanks for pointing that out. I have added the version to the Q. Excel 2003 SP3. – bentayloruk Mar 22 '11 at 12:36
1  
when you actually look at the formula does it match what it would if you explicitly declared it? e.g. =sum("A1:E20") – datatoo Mar 25 '11 at 0:38
1  
You've checked for hidden rows, are there any hidden columns? – Diem Mar 26 '11 at 14:36
1  
@bentayloruk in that case write your own answer to not only check for hidden rows but also for hidden columns :) – oleschri Jul 20 '11 at 16:49
show 2 more comments
feedback

2 Answers

up vote 0 down vote accepted

The following things can cause the quick sum calculation to be wrong:

  • Hidden rows
  • Hidden columns

How to show or hide columns and rows in Excel

Note: my problem was caused by a hidden column *embarrassed look*. Thanks to @Diem for pointing this out in the question comments. In the absence of any other correct answers, I will mark this as the answer for now.

link|improve this answer
feedback
up vote -1 down vote

This may be a numerical accuracy issue. Spreadsheet software are known for poor numerical accuracy, see http://www.jstatsoft.org/v34/i04/paper.

link|improve this answer
There's a slight difference between numerical accuracy/floating point precision and being of by 100 percent. – Bobby Mar 26 '11 at 20:14
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.