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

Please forgive my ignorance regarding "reply" protocol but I have posted additional information that may help in a reply to another member on the thread.  Will everyone on the thread be aware of my reply or just the people I reply to directly?

Again I apologize if I am "over posting"

Thank you,

Jeff

Answer 2

Not very likely. More likely somebody entered data and did a save. Them entered more data and exited without saving (i.e. ignoring the save warning)

best wishes

Answer 3

If people have opted to be notified of updates to this thread (and I think most of us do), then they will be notified no matter to whom you respond.

And I haven't had time to review the methodology used and how it might be subverted to produce the issue, but a partial save is unlikely, in my opinion.

Answer 4

JM wrote:

It's like the spreadsheet partially saved?  Is that possible

Anything is "possible".  The question is:  is it "likely"?  Well, it is more likely than an errant neutrino that bounced around the file on disk and altered just the right bits.

But before we pursue that theory, let's stick with more reasonable actions that we know can cause the problem.  I offered one:  setting Application.EnableCalculation=False.  There might be others; but none that I have found yet.

So let's look for that before ruling it out.

JM wrote:

This worksheet is updated every 2 weeks by one of our "advanced beginner" excel users [...].  The user updates it every two weeks by either manual input or copy and paste from another workbook.  [....] She discovered the calculation issue when she opened it up yesterday. 

First, be sure to save a copy of the broken file.  Use Windows to set it to Read Only so it cannot be modified.  Right-click on the file icon, click on Properties, and select Read Only.

If you have lost the broken file, download your copy on drive.google.com.

Second, confirm that ctrl+alt+F9 remedies the problem, and the problem does not persist after you save the file after pressing ctrl+alt+F9.  Right?

If that is the case, do you still want to pursue the root cause of the broken file?

I think it is very likely you will never find the root cause.

But if you want to look, the work (pursuing the problem) must be done on the computer where the updates took place and in the same user environment, if there is more than one on that computer.

Open Excel, press alt+F11 to go into VBA, and press ctrl+R if necessary to open Project Explorer.  If you see VBA Project (PERSONAL.XLS...), click on the "+" symbols so you can see the

Microsoft Excel Objects and any Class Modules and (regular) Modules.  Use Find or manually search for EnableCalculation.

If there is no VBA Project(PERSONAL.XLS...) in Project Explorer, talk to the other user.  Did she delete a personal.XLS... files?  Could someone else have?  Did she last update the file on another computer?  Did she use a different version of Excel for the last update?

Answer 5

I wrote:

Open Excel, press alt+F11 to go into VBA, and press ctrl+R if necessary to open Project Explorer.  If you see VBA Project (PERSONAL.XLS...), click on the "+" symbols so you can see the

Microsoft Excel Objects and any Class Modules and (regular) Modules.  Use Find or manually search for EnableCalculation.

I forgot to ask the "more obvious" questions first.

Did the original broken file have worksheets that you or the other user deleted before you uploaded it to drive.google.com?

Did it have macros (other the personal.xls...) that you or the other user deleted before you uploaded the file to drive.google.com?