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

I do not have a definitive answer but I strongly suspect that the Is Dirty flag that signals a formula to be recalculated was dropped somehow. Since simple SUM(...) and this+that formulas are not volatile (i.e. recalculated for every calculation cycle in the workbook) they would only be recalculated if reasserted.

Solution: use Find & Replace to globally search for = (the equals sign) and replace it with = (the same thing). This essentially rewrites every formula in the worksheet thus resetting the cell's Is Dirty flag. I've tested your worksheet this way and it seems to correct the errant behavior even after saving, closing and reopening.

Sorry if I cannot be more specific but without knowing the complete history of the workbook/worksheet I can only pass along what I've found to correct the problem without completely understanding the problem itself.

Answer 2

Interesting. Opening the file in 2013, the cell shows the correct result straight away. Opening in 2010 it shows the wrong result. 

Also, in 2010, selecting cell G10 and hitting the DEL key changes  F10 to the correct formula result.  The same goes for the other cells in the table, save for row 9. The results are wrong in rows 7, 8, 10, 11, and 12.  Deleting the cell contents of column G in these rows produces correct results. 

G9 has a different cell format (a border is visible at the left edge). 

I have a slight suspicion that the contents of the cells G7, G8, G10 to G12 have been copied and pasted and are not truly empty cells. At the least, they are corrupt in some way. Even changing their formatting causes a re-calculation of the formulas in column F and a correct result. 

So, how did these cells get populated? It could be that they contain some obscure non-printing character if the table has been copied from somewhere else.

Answer 3

F9 only recalculates formulas the have changed since the last calculation.  CTRL+ALT+F9 OR CTRL+SHIFT+ALT+F9 both result in the formula in those cells being updated and calculating properly.  Both of those will recalculate all formulas; the second also rechecks dependent formulas.

So I think we need to know how those values and formulas got into the cells in the first place, in order to figure out what happened.

Answer 4

JM wrote:

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

JM wrote:

Here's the link to the file... thanks for looking at this.

https://drive.google.com/file/d/0B8LJFJuWB-\_bZWFHYVZwaWNjZkU/edit?usp=sharing

The following scenario demonstrates one way to get the Excel file into that state.

1. Start with a new Excel file in a new instance of Excel.
2. Enter 1, 10, 100, 1000 into A1:D1, and enter =SUM(A1:D1) into E1.  E1 displays 1111.
3. In the VBA Immediate Window (press alt+F11, then ctrl+G), execute the following statement:

ActiveSheet.EnableCalculation = False 4. In Excel, clear D1 (1000).  Note that E1 still displays 1111, as expected. 5. Save the file, and exit Excel. 6. Re-open the Excel file.  Note that E1 still displays 1111 (!), even though Automatic calculation mode is set, and Print ActiveSheet.EnableCalculation displays True in VBA. 7. Press ctrl+alt+F9.  Note that E1 now displays 111.

Of course, how step #3 actually happened is anyone's guess.  (I have a strong suspicion.)

Answer 5

Thank you for all the responses.

This worksheet is updated every 2 weeks by one of our "advanced beginner" excel users so I asked her to explain her process and background of the file.  (for reference; I am an intermediate user)

Here is what I found:

                       The worksheet was set up 3 or 4  years ago using 2003.  The user updates it every two weeks by either manual input or copy and paste from another workbook.   She prints out the worksheet and saves the workbook.   She discovered the calculation issue when she opened it up yesterday. 

Upon reviewing the last few print outs we discovered that the data in the current (flawed calculation) version is a combination of the input data from 7/31/14 (two updates ago) and totals in the formula cells from 8/15/14 (the last update).    i.e.  from Row 10  of my original question;

                      Printout dated 7/31/14>  893.67+110.10+700.50+635.01-0 = 2340.28

                      Printout dated 8/15/14>  900.15+113.78+700.50+616.88-0 = 2331.31

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