A fault with the Excel Sum function

  • Thread starter Thread starter Ron J
  • Start date Start date
R

Ron J

I am trying to find out where at Microsoft I can submit a
fault in the "SUM" function. There is a problem when using
two separate formats of the Sum command to sum the same
data - 2 different answers. The 9th decimal position is
off by 1 with he different method of using the same
function.

Who can I talk with or submit the file to?

Thanks

Ron
 
I am trying to find out where at Microsoft I can submit a
fault in the "SUM" function. There is a problem when using
two separate formats of the Sum command to sum the same
data - 2 different answers. The 9th decimal position is
off by 1 with he different method of using the same
function.

Who can I talk with or submit the file to?
Click to expand...

The straight answer is that you call Microsoft's customer support number and pay
for the priviledge of reporting bugs. If Microsoft acknowledges it's a bug, they
credit the credit card charge, probably after bilking you and/or your credit
card issuer for a few days of float.

For this particular situation, it's unlikely to be a bug per se, rather an
instance of floating point rounding error. What are the *EXACT* SUM formulas?
The order of evaluation *DOES* affect the results Excel produces, but this is a
failing shared by all software that uses binary floating point arithmetic. What
you need to understand is that while addition and multiplication of true real
numbers are commutative and associative, addition and multiplication of binary
floating point numbers are only commutative, not associative.
 
-----Original Message-----
...

The straight answer is that you call Microsoft's customer support number and pay
for the priviledge of reporting bugs. If Microsoft acknowledges it's a bug, they
credit the credit card charge, probably after bilking you and/or your credit
card issuer for a few days of float.

For this particular situation, it's unlikely to be a bug per se, rather an
instance of floating point rounding error. What are the *EXACT* SUM formulas?
The order of evaluation *DOES* affect the results Excel produces, but this is a
failing shared by all software that uses binary floating point arithmetic. What
you need to understand is that while addition and multiplication of true real
numbers are commutative and associative, addition and multiplication of binary
floating point numbers are only commutative, not associative.
Click to expand...
many decimal positions due to previous multiplication and
division in associated cells. To check where the fault
lies I copied only the totals into another spreadsheet as
static numbers (using paste values)without any formulas
existing in the new file, closing and saving both and
reopening the new to ensurer there were no links.

Next I summed this data in two different formats.

=sum(a1:f1, a2:f2)
and
=sum(a1:f1) + sum (a2:f2)
There was a differnce in the result at the ninth decimal
position which caused my "check for error" flag to pop.

It is probably a floating point error as you said but I am
using the "check for error" cell on this type of addition
for many rows (25 checks) and for many month (2-3 years).
This this is the first time the error check uncovered
this issue.

Thanks,

Ron
 
...
...
Next I summed this data in two different formats.

=sum(a1:f1, a2:f2)
and
=sum(a1:f1) + sum (a2:f2)
There was a differnce in the result at the ninth decimal
position which caused my "check for error" flag to pop.
Click to expand...

By 'ninth decimal position' do you mean the 9th decimal place to the right of
the decimal point? If so, how many decimals do you have to the left of the
decimal point?
It is probably a floating point error as you said but I am
using the "check for error" cell on this type of addition
for many rows (25 checks) and for many month (2-3 years).
This this is the first time the error check uncovered
this issue.
Click to expand...

It's hard to say which of your two formulas would be more accurate. You may want
to consider the old fashioned way of guarding against rounding error - instead
of X=Y, use ABS(X-Y)<SomethingSmallButStillPositive, where the right hand term
could be 1E-6.
 
Back
Top