Excel Formula doesn't calculate correctly

Question

Hope someone can help point me in the right direction.

This formula is not calculating correctly in my Excel 2010  workbook.

  =SUM(B10:E10)-G10 

Evaluate formula shows:

           1st                    =SUM(B10:E10)-G10    

           2nd                              =2,340.28-G10

           3rd                               =2,340.28-0

           4th                               =2,331.31

Total should be   2,340.28

  The workbook is set to automatically calculate  and is the only workbook open.  Hitting F9 does nothing.  Closing and re-opening does nothing.  If I re-enter or change one of the amounts from the input cells the formula will automatically recalculate correctly.  If after making a change I hit undo the formula stays correct and does not go back to the incorrect calculation.

Can any one tell me what may be causing this problem?   My concern is that I have hundreds of workbooks with formulas that I assume are calculating correctly and it was only dumb luck that I happened to notice this slight discrepancy shown above.

Thank you.

Answer 1

Thanks for all your help and insight.  

We have a lot of variables that could have affected the worksheet; we have 3 different versions of Excel, the user works on at least 2 separate workstations, the file is saved on a network drive that seems to go down every other day so it's hard to know exactly what happened.

I've confirmed that ctrl+alt+F9 fixes the issue so we will go with that,  thank you.

I am curious about a couple of things;  where did you see personal.xls?  

Also, what did you mean when you said,   "Of course, how step #3 actually happened is anyone's guess.  (I have a strong suspicion.)" when listing the steps to get the spreadsheet into the state it was in?   I'm interested to know your suspicion.

Answer 2

JM wrote:

I am curious about a couple of things;  where did you see personal.xls?  

By default, in the folder drive:\Documents and Settings*loginName*\Application Data\Microsoft\Excel\XLSTART.

I just discovered a security loophole when using XL2003, which I will not discuss further for obvious reasons.  It might also exist in XL2007 and later, but I don't see it because of the (very conservative) way in which I set up my Trust Center options.

In any case, I suggest that you ensure there are no files in the XLSTART folder on any computer where you might open the file in question.

JM wrote:

Also, what did you mean when you said,   "Of course, how step #3 actually happened is anyone's guess.  (I have a strong suspicion.)" [...]?   I'm interested to know your suspicion.

They are implicit in the questions I asked subsequently.

JM wrote:

the file is saved on a network drive that seems to go down every other day

In theory, that does increase the likelihood that an "incomplete save" is the root cause of the problem.  I would hope that Excel or Windows takes steps to ensure that cannot happen.   There are design methodologies that ensure the integrity of networked files.  But I cannot say whether or not Microsoft implements them; and whether or not there are ways for a user to unwittingly circumvent them.

Answer 3

I'm having the same problem but with a very simple formula; the values in the cells equal $1.00 in difference but excel is calling it $1.01.  No amount of number changing or adjusting will correct it.