Excel problem with Sum Function

Question

Try using the SUM Function to add these numbers.....

-10, .9, 2.4, 3.3, 3.2, .2

or

-10, 2.3, 2.4, .7, .6, .5, 2.1, 1, .4

or

-12, .8, 7.4, 3, .8

the order seems to be important also

column or row does not seem to matter

Answer 1

Try using the SUM Function to add these numbers.....

-10, .9, 2.4, 3.3, 3.2, .2

or

-10, 2.3, 2.4, .7, .6, .5, 2.1, 1, .4

or

-12, .8, 7.4, 3, .8

the order seems to be important also

column or row does not seem to matter

OK, this is old decimal vs binary counting problem.  People count in powers of 10, computers count in powers of 2.  Powers of 2 have problems representing decimals and fractions.

The sequences are supposed to sum to 0, but due to the problem representing decimals, there are tiny variations.  If you round the sum to 1 decimal place, the problem goes away.

You can do this either by using the Round() function, or by defining the cell/column format as number with 1 decimal

First row is Sum() and Round()

Second row is using cell format, 1 decimal.

Here are some of the "standard" descriptions of the problem

! Floating-point arithmetic may give inaccurate results in Excel

https://support.microsoft.com/en-gb/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel

This article discusses how Microsoft Excel stores and calculates floating-point numbers. This may affect the results of some numbers or formulas because of rounding or data truncation.

Microsoft Excel was designed around the IEEE 754 specification to determine how it stores and calculates floating-point numbers. IEEE is the Institute of Electrical and Electronics Engineers, an international body that, among other things, determines standards for computer software and hardware. The 754 specification is a very widely adopted specification that describes how floating-point numbers should be stored in a binary computer. It is popular because it allows floating-point numbers to be stored in a reasonable amount of space and calculations to occur relatively quickly. The 754 standard is used in the floating-point units and numeric data processors of nearly all of today's PC-based microprocessors that implement floating-point math, including the Intel, Motorola, Sun, and MIPS processors.

! (Complete) Tutorial to Understand IEEE Floating-Point Errorshttp://support.microsoft.com/kb/42980

Floating-point mathematics is a complex topic that confuses many programmers. The tutorial below should help you recognize programming situations where floating-point errors are likely to occur and how to avoid them. It should also allow you to recognize cases that are caused by inherent floating-point math limitations as opposed to actual compiler bugs. 

ET R 0 Ribbon.docx

! Excel addition problems - Microsoft’s responsehttps://office-watch.com/2008/excel-addition-problems-microsofts-response/

Here's Microsoft's response to the anomalies found in Excel's adding up. ET R 0 Ribbon.docx

! Floating-point arithmetic may give inaccurate results in Excelhttps://support.microsoft.com/en-us/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel

This article discusses how Microsoft Excel stores and calculates floating-point numbers. This may affect the results of some numbers or formulas because of rounding or data truncation.

ET R 0 Ribbon.docx

! Microsoft's ‘Get out of jail free’ card for Excelhttp://news.office-watch.com/t/n.aspx?articleid=615&zoneid=9

Microsoft has a standard response to any maths anomalies in Excel - KB214118 - we have a look at what it says and doesn't say.

ET R 0 Ribbon.docx

! MS Fixes Excel (a) Calculation Bug – 2007 10 11http://www.eweek.com/c/a/Enterprise-Applications/Microsoft-Fixes-Excel-Calculation-Bug/

Microsoft confirms it has fixed an Excel calculation results bug, but declines to fully explain the cause.

ET R 0 Ribbon.docx

**! Understanding Floating Point Precision, aka “Why does Excel Give Me Seemingly Wrong Answers?”**2008 04 10

https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/

We sometimes get mails from our customers claiming to have found a calculation error in Excel, when in fact the calculation isn’t wrong, but the side effects of binary floating point precision make it seem that way. Today’s author: Jessica Liu, a Program Manager on the Excel team, discusses the way Excel performs calculations, explains why sometimes you see answers you may not expect, and provides some tips on how to avoid rounding issues.

**Precision and Number Display**  

https://excel.tips.net/T001982_Precision_and_Number_Display.html

Do you need to work with very precise numbers in your worksheets? If you do, then it is imperative you understand precision within Excel.

ET R 0 Ribbon.docx

$ **Errors When Subtracting** ****http://excelribbon.tips.net/T006176_Errors_When_Subtracting.html

When you subtract two numbers from each other, you have a certain expectation of what Excel should deliver. What if you get something that is not quite what you expected, however? 

ET R 0 Ribbon.docx