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'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.

Format all the cells as "General", and post here what you see.

Answer 2

Having upgraded our home computer to Office 2013 (aka Office 365 Home) last week, I discovered this problem and did a search. Found this thread which leaded me to solve my worksheet's =SUM errors.

Yes, I copied the original text from another program into a blank EXCEL 2013 worksheet and noted that the SUM total doesn't sound right.

Copy & Paste output:- 

-$306	$1,270
$     0	$   540
$  115	$   460
$  270	$1,080
$  189	$   750
$     0	$239.10
-$306.00	$2,589.10	this row =SUM

Pressing ALT + F11 brought up the VBA tool to see that EnableCalculation (property) is already TRUE. If I save the new worksheet and reopen it, the =SUM values in both columns A and B (row 8) are still wrong.  As for CTRL + ALT + F9, no change.

Thanks to RonR (16th Dec 2014), changing all cells to format 'General' revealed the cause. My worksheet now works as intended.

In my new worksheet, cells A1 thru A7, plus B3, B4 & B6 had the '$' typed manually (along with 2 or more blank characters) before the numerical values which was originally done in the other program (a note-taking tool) for the values to line up visually "on the go, when using desktop / laptop / mobile devices". Once the '$' and empty chars were stripped / or simply edited to '$ ' (only 1 blank char between $ and numerical values), the SUM values now match what I manually calculated. That's it.

Answer 3

Glad you worked it out.  Unfortunately, sometimes in Excel what we think we see is not always the same as what Excel is seeing.

Answer 4

Excel isn't so much for calculating I guess. Try this =INT((6.05-6)*100)

Answer 5

Rolf wrote:

Excel isn't so much for calculating I guess. Try this =INT((6.05-6)*100)

Try:  =INT(ROUND((6.05-6)*100,2)).

Most decimal fractions cannot be represented exactly in the binary form that Excel uses internally.  Consequently, most non-integer arithmetic results in infinitesimal differences from mathematical expectations.

For example, (6.05-6)*100 is about 4.99999999999998.  And INT(...) correctly returns 4.

In general, if you expect non-integer arithmetic to be accurate to some number of decimal places, explicitly round.

PS....  Of course, in that example, =ROUND((6.05-6)*100,0) would suffice.