#1   Report Post  
MattShoreson
 
Posts: n/a
Default Excel weirdness!!!!


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

  #2   Report Post  
bj
 
Posts: n/a
Default

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


  #3   Report Post  
Kassie
 
Posts: n/a
Default

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


  #4   Report Post  
Harald Staff
 
Posts: n/a
Default

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"
skrev i melding
news:MattShoreson.1rfumb_1120133131.8632@excelforu m-nospam.com...

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



  #5   Report Post  
MattShoreson
 
Posts: n/a
Default


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!!!!!!!


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=383481



  #6   Report Post  
Jerry W. Lewis
 
Posts: n/a
Default

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!!!!!!!


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
"Group" function very slow with Excel 2003 :( ... While very quick with Excel2000 :O) Alain79 Excel Discussion (Misc queries) 4 June 14th 05 08:34 AM
Stop Excel Rounding Dates leinad512 Excel Discussion (Misc queries) 1 April 20th 05 05:19 PM
Hints And Tips For New Posters In The Excel Newsgroups Gary Brown Excel Worksheet Functions 0 April 15th 05 06:47 PM
Excel error - Startup (and Acrobat PDFMaker) gxdata Setting up and Configuration of Excel 0 February 4th 05 04:44 AM
Excel 2002 and 2000 co-install. Control Which Starts ? cnuk Excel Discussion (Misc queries) 2 January 17th 05 09:07 PM


All times are GMT +1. The time now is 12:42 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"