Unexpected rounding issue with Sum of selected cells

[ou81aswell]

I have a column (B) with 113 rows of data in it. B1 is a text cell and contains the name of the column. B2:B113 contain either blanks or dollar values. Sum(B2:B113) is 0.00.

There are 14 blanks, 30 zeros (0.00), 14 negative values and 54 positive values in the column and there are no hidden, blank or phantom rows in the sheet. The non-zero numbers range from -1,600,000.00 to +1,600,000.00. The sheet is in a .XLS workbook.

If I click column letter B, Excel 2010 highlights the entire column and displays the average, count and sum of the highlighted values in Excel's status bar. However, there appears to be a rounding error in that it displays the sum as being -7.27596E-11 instead of 0.00. This is when the entire column is selected.

If I highlight only B2:B113, leaving B1 (the column's name) and B114 and below un-highlighted, it displays the sum correctly as 0.00.

Why would it display a different sum for what should essentially be the same selection?

Thanks

 

[Jerry Sullivan]

Hi ou81aswell,

This thread might help explain the result of the floating-point limitation you're observing.

http://www.mrexcel.com/forum/showthread.php?t=587620

 

[ou81aswell]

Thanks. That thread talks about the rounding differences between Excel's SUM and SUMIF functions.

When displaying the sum of the selected cells in the status bar, does Excel use the SUMIF function if the entire column was selected and the SUM function if only a part of the column was selected? That would explain the behaviour I am seeing.