|
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 |
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 |
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 |
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 |
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 |
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!!!!!!! |
| All times are GMT +1. The time now is 12:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com