excel is calculating wrong again.

P

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.
 
R

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
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top