Excel weirdness!!!!

[MattShoreson]

I have a text file containing values that I import into excel.

Once the values are in an excel sheet I do a sum of them all. (currency - 2dp)

The final sum is displaying as 441992.489999999990000 - why would this be?
None of the individual values show a greater degree of precision that 2dp!!!
In the text file there are no values with more than 2dp!

Why is excel calculating incorrectly.
I know I can do rounding etc. but what I want to know is why excel is playing up!

CIA,Matt.

[bj]

The algorthm Excel uses for non integer numbers is a possible cause
I would use the round function to get the display you want.

"MattShoreson" wrote:

>
> I have a text file containing values that I import into excel.
>
> Once the values are in an excel sheet I do a sum of them all.
> (currency - 2dp)
>
> The final sum is displaying as 441992.489999999990000 - why would this
> be?
> None of the individual values show a greater degree of precision that
> 2dp!!!
> In the text file there are no values with more than 2dp!
>
> Why is excel calculating incorrectly.
> I know I can do rounding etc. but what I want to know is why excel is
> playing up!
>
> CIA,Matt.
>
>
> --
> MattShoreson
> ------------------------------------------------------------------------
> MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
> View this thread: http://www.excelforum.com/showthread...hreadid=383481
>
>

[Kassie]

Hi

The esiest way is to format your cells as currency, and then do the sum.
Otherwise, after doing the sum, still do the formatting part, but change the
number of decimals to 2.

"MattShoreson" wrote:

>
> I have a text file containing values that I import into excel.
>
> Once the values are in an excel sheet I do a sum of them all.
> (currency - 2dp)
>
> The final sum is displaying as 441992.489999999990000 - why would this
> be?
> None of the individual values show a greater degree of precision that
> 2dp!!!
> In the text file there are no values with more than 2dp!
>
> Why is excel calculating incorrectly.
> I know I can do rounding etc. but what I want to know is why excel is
> playing up!
>
> CIA,Matt.
>
>
> --
> MattShoreson
> ------------------------------------------------------------------------
> MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
> View this thread: http://www.excelforum.com/showthread...hreadid=383481
>
>

[Harald Staff]

Hi Matt

See http://www.cpearson.com/excel/rounding.htm for the theory on this.
Acknowledge the the relative size of the error and the worst case scenarios
caused by this inaccuracy, and think no more about it.

HTH. Best wishes Harald

"MattShoreson" <[email protected]>
skrev i melding
news:[email protected]...
>
> I have a text file containing values that I import into excel.
>
> Once the values are in an excel sheet I do a sum of them all.
> (currency - 2dp)
>
> The final sum is displaying as 441992.489999999990000 - why would this
> be?
> None of the individual values show a greater degree of precision that
> 2dp!!!
> In the text file there are no values with more than 2dp!
>
> Why is excel calculating incorrectly.
> I know I can do rounding etc. but what I want to know is why excel is
> playing up!
>
> CIA,Matt.
>
>
> --
> MattShoreson
> ------------------------------------------------------------------------
> MattShoreson's Profile:
http://www.excelforum.com/member.php...fo&userid=3472
> View this thread: http://www.excelforum.com/showthread...hreadid=383481
>

[MattShoreson]

cheers guys for the response.

Have gone with rounding as values are all currencies.

Little bit worrying though as it makes you unsure of your faith in comps in general to perform accurate calculations!!!!!!!

[Jerry W. Lewis]

The math is correct (you can trust it), but the orginal numbers
themselves had to be approximated, with the result that the final result
is only approximate.

Excel and almost all other general purpose computer software do math in
binary, but the only 2-decimal numbers that can be exactly represented
in binary are .00, .25, .50, and .75. All others are non-terminating
binary fractions (just as 1/3 is a non-terminating decimal fraction).
As a result, all other decimal fractions must be approximated. The
degree of approximation is governed by the IEEE standard that is
discussed in Chip Pearson's article that Harald suggested to you.

To see what happened subsquently, consider a hypothetical decimal
computer that carries only 4 decimal figures
=((123+1/3)-123)-1/3
would be calculated as
=(123.3-123)-0.3333
=0.3-0.3333
=-0.0333
instead of zero. Once you introduce finite precision approximations,
many calculations will reveal that the numbers were not exactly represented.

Given that the issue is approximation to inputs, not the subsequent
math, Rounding the final result is not obscuring a serious flaw, as you
seem to fear. Another approach would be to work in pennies instead of
dollars (i.e. multiply everything by 100), since integers are exactly
represented.

Jerry

MattShoreson wrote:

> cheers guys for the response.
>
> Have gone with rounding as values are all currencies.
>
> Little bit worrying though as it makes you unsure of your faith in
> comps in general to perform accurate calculations!!!!!!!