excel is calculating wrong again.

[prpball]

Calculation issue not fixed.
When calculating a range of numbers I get this:
114,642.55 good

When I add another range of numbers I get this:
165,707.09 good

Now when I try and subtract those two summed fields 165,707.09 from
114,642.55 is get this:
51,064.46999999 wrong
I should get this 51,064.47.

How can this be?
I know I can format the field to show the 51,064.47 but the value is
51.064.46999 which after thousands of additions and subtractions that
fraction of a decimal will add up. I am on service pack 2 for office.
It works fine if you just type in the numbers into two fields but when you
subtract the cells with the functions in them, they do not work.

 

[Ron Rosenfeld]

Calculation issue not fixed.
When calculating a range of numbers I get this:
114,642.55 good

When I add another range of numbers I get this:
165,707.09 good

Now when I try and subtract those two summed fields 165,707.09 from
114,642.55 is get this:
51,064.46999999 wrong
I should get this 51,064.47.

How can this be?
I know I can format the field to show the 51,064.47 but the value is
51.064.46999 which after thousands of additions and subtractions that
fraction of a decimal will add up. I am on service pack 2 for office.
It works fine if you just type in the numbers into two fields but when you
subtract the cells with the functions in them, they do not work.

Search the MSKB for "rounding errors". What you see is related to the IEEE
standard for double-precision numbers; and also to the impossibility of
representing certain values exactly in a finite number of binary digits.
(Similar to the impossibility of representing 1/3 in a finite number of decimal
digits).
--ron

 

[Bill Sharpe]

Calculation issue not fixed.
When calculating a range of numbers I get this:
114,642.55 good

When I add another range of numbers I get this:
165,707.09 good

Now when I try and subtract those two summed fields 165,707.09 from
114,642.55 is get this:
51,064.46999999 wrong
I should get this 51,064.47.

How can this be?
I know I can format the field to show the 51,064.47 but the value is
51.064.46999 which after thousands of additions and subtractions that
fraction of a decimal will add up. I am on service pack 2 for office.
It works fine if you just type in the numbers into two fields but when you
subtract the cells with the functions in them, they do not work.

See Excel help for "set rounding precision."

Bill