Excel Sum Function Returns Incorrect Result

Question

I'm using a simple Sum function, but it's returning the wrong result.  All my numbers are only two decimal places at most, but the Sum calculation is returning a number with values starting in the 13th decimal place.  I've tried reformatting to number, currency, and accounting and I still get the same result.  I opened a new workbook and just typed the numbers in and I still get the same result with using the Sum function, or if use a formula to just add the fields.  What's going on?

	395.55000000000000000000
	-9837.00000000000000000000
	-2150.00000000000000000000
	-1670.00000000000000000000
	-1480.00000000000000000000
	2170.00000000000000000000
	1603.00000000000000000000
	1220.00000000000000000000
	1100.00000000000000000000
	1080.00000000000000000000
	887.00000000000000000000
	845.00000000000000000000
	840.00000000000000000000
	718.00000000000000000000
	708.00000000000000000000
	680.00000000000000000000
	648.00000000000000000000
	614.00000000000000000000
	499.00000000000000000000
	448.00000000000000000000
	394.00000000000000000000
	287.00000000000000000000
	0.45000000000000000000
SUM	-0.00000000000072758466

Answer 1

What you are witnessing is rounding of IEEE formatted numbers (more below). It's basically noise. To resolve your specific issue, use this formula:

=ROUND(SUM(Range),2)

This will round the sum to two decimal places which removes the noise.

Kevin

---

When comparing the results of a numeric calculation to a possible constant outcome, if the result of the comparison is unexpected, the problem might not be with the formulas but rather with how Excel handles numbers. Excel is known to introduce a very small amount of floating point noise into calculations which, while not affecting the number's significant digits, can create problems when comparing the results of calculations with constants. Use the following formula to remove insignificant digits or floating point noise from any number:

   =ROUND(A1,SignificantDigits-INT(LOG(A1)))

where SignificantDigits is the number of significant digits applicable to the problem being solved.

Why is it necessary to remove insignificant digits? Excel uses the IEEE 64-bit floating point representation of numbers to store numeric values. This format works very well as long as all values are integers. But when values have decimal parts (digits to the right of the decimal point) then very small inaccuracies or floating point noise can creep into formula results. Consider the following task: remove the whole part and first decimal digit from a decimal number using simple math. Depending on what the original number is and the formula used, the final result may or may not be exactly what is expected. Here are three different formulas that produce the same result (almost):

   =A1-INT(A1*10)/10

   =(A1*100-INT(A1*10)*10)/100

   =A1-TRUNC(A1,1)

Here are the results of applying these three different formulas on the same set of numbers (the original number is listed first followed by the results of the three formulas in the same order as listed above):

1.00   0.00000000000000000   0.00000000000000000   0.00000000000000000

1.01   0.01000000000000000   0.01000000000000000   0.01000000000000000

1.02   0.02000000000000000   0.02000000000000000   0.02000000000000000

1.03   0.03000000000000000   0.03000000000000000   0.03000000000000000

1.04   0.04000000000000000   0.04000000000000000   0.04000000000000000

1.05   0.05000000000000000   0.05000000000000000   0.05000000000000000

1.06   0.06000000000000010   0.06000000000000000   0.06000000000000010

1.07   0.07000000000000010   0.07000000000000000   0.07000000000000010

1.08   0.08000000000000010   0.08000000000000000   0.08000000000000010

1.09   0.09000000000000010   0.09000000000000010   0.09000000000000010

1.10   0.00000000000000000   0.00000000000000014   0.00000000000000000

The results illustrate that, depending on the original number and the formula used, the result may or may not be the exact result expected. Subsequent evaluation of the "erroneous" results such as comparing them to constants will yield unexpected results unless the "erroneous" insignificant digits are removed. For example, this formula returns True:

   =IF(1.01-INT(1.01*10)/10=0.01, True, False)

while this returns False:

   =IF(1.09-INT(1.09*10)/10=0.09, True, False)

The most straightforward way to solve this problem is to first decide on how many significant digits are relevant and then round all results to that number of significant digits. The first formula introduced above illustrates how to do this rounding. Note that the number of significant digits in any number are not the number of non-zero digits to the left and/or right of the decimal point, but the total number digits starting with the highest order digit. The formula works by considering the number of digits in the whole part (digits to the left of the decimal point) and rounding n digits to the right of the left-most digit, whole or decimal. The results of the formula used with four significant digits are listed below.

   654321.123456789 -> 654300

     54321.123456789 -> 54320

       4321.123456789 -> 4321

         321.123456789 -> 321.1

           21.123456789 -> 21.12

             1.123456789 -> 1.123

             0.123456789 -> 0.1235

             0.023456789 -> 0.02346

             0.003456789 -> 0.003457

             0.000456789 -> 0.0004568

             0.000056789 -> 0.00005679

             0.000006789 -> 0.000006789

             0.000000789 -> 0.000000789

Answer 2

Hi Jereme,

Do you have any update?

Regards,

Yoga