Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|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

2 Answers

up vote 1 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.

share|improve this answer
add comment

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

share|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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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