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.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions

 

[David Biddulph]

Well, to start with, 165,707.09 - 114,642.55 is 51064.54, not 51064.47, and
hence if you subtract 165,707.09 from 114,642.55 you should get MINUS
51064.54.

If you get the round number (to 2 places) displayed when you type the
numbers in, but you don't when you use your calculated values, then this is
telling you that your 165,707.09 and 114,642.55 are not exactly those
values. Instead of displaying them to 2 decimal places, change the display
to 8 or 9 decimal places and have a look at what it then tells you.

There is, however, always the danger that numbers don't show exactly what
you expect, because Excel does its calculations in binary, and there is no
exact binary representation of numbers like 0.55 or 0.09. You can get an
exact binary representation of 0.5 or 0.25 or 0.375, but not 0.1 or 0.01.
Hence there will always be small rounding errors. If you are working with
numbers that you expect to be exact multiples of 0.01, use the ROUND
function from time to time in your calculations, hence instead of =A1+B1 you
can use =ROUND(A1+B1,2).

 

[Gary Brown]

Check out Chip Pearson's explanation of how Excel rounds...
http://www.cpearson.com/Excel/rounding.htm

 

[Chip Pearson]

I should get this 51,064.47.

No you shouldn't.

Check your numbers again and the formulas that feed them. In Excel
2007 (and confirmed on my HP48GX and HP12C calculators), the
subtraction

165,707.09 - 114,642.55 = 51,064.54, not 51,064.469... -> 51,064.47


Since you haven't posted the formulas that yield the two subtracted
numbers, it is rather difficult to diagnose the problem. I dummied up
some formulas that result in the desired numbers, and when subtracted
they yield the correct result.

There is always some rounding in Excel (and nearly all other
software), and that can usually be controlled by judicious use of the
ROUND* functions (or, dangerously, via the Precision As Displayed
setting).



Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]