This is a simple exercise to transform decimal angles to degrees, minutes and seconds.
A1=25.4 (decimal degrees)
B1=INT(A1)=25 (degrees)
C1=(A1-B1)*60= should be 24 (minutes)
C1=23.999999999999900000, this is supposed to be 24.000
A1-B1 gives 0.399999999999999.
I'm not using the ROUND() function.
Any reason for this rounding error? Am I doing something wrong here? Thanks.
Unable to duplicate your error. When I input data as shown in your post, I get 24.0000000000 in C1 and
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Reason: standard floating point error https://www.excelforum.com/groups/ma...nd-errors.html
Fix: use ROUND() or other strategy to minimize floating point errors.
Originally Posted by shg
Try with 15 decimals
Thanks, was thinking of using 4 or 5 decimals with ROUND() but wanted to know the reason. Thanks !
The rounding "error" is due to the fact that Excel uses 64-bit binary floating-point to represent numbers. Decimal numbers are approximated by a sum of 53 consecutive powers of 2 ("bit"). The highest (left-most) power of 2 is determined by the magnitude of the number.
Consequently, most decimal fractions (and integers greater than 2^53) cannot be represented exactly. And the approximation of a particular decimal fraction (e.g. 4/10) varies depending on the magnitude of the integer part.
Contrary to most online information, the precision of the binary sum is not limited to 15 significant digits. But Excel formats only up to 15 significant digits (rounded).
For your example, the exact values are shown by their equivalent decimal representation on the right.
I use period for the decimal point and comma to demarcate the first 15 significant digits.
Please Login or Register to view this content.
And yes, in general, when we expect a calculation to be accurate to n decimal places, we should explicitly round the calculation (usually by using ROUND) to that number of decimal places, not to an arbitrary number of decimal places like 10, as some people suggest.
Apparently you formatted to display only 10 decimal places; 12 significant digits. Try formatting to 13 decimal places; 15 significant digits.
Thanks joeu2004, quick and to the point. This explanation explained some previous issues too. ROUND(#,4) did the trick.
By the way, INT(A1) does not give the wrong answer, contrary to your title. Instead, it is the subtraction that gives the unexpected answer.
However, there are circumstances where INT() does indeed give the wrong answer. For example, INT(0.999999999999998*20) returns 20. It should return 19, as it does in VBA.
The problem: apparently INT() rounds its operand to 15 significant digits, then truncates it. 0.999999999999998*20 is exactly 19.9999999999999,60920149533194489777088165283203125, which rounds to 20.0000000000000.
This has significance when using an expression of the form INT(n*RAND())+1 to choose a random index of an array, with the intent that 1 <= index <= n is true. It is extremely rare for RAND() to return 0.999999999999998 or larger. But it did trip me up once.
PS.... I'm not even sure this is possible with RAND() in Excel 2010 and later. I encountered the problem using Excel 2003, which used a different algorithm for RAND(). Nevertheless, INT() has the problem described in Excel 2010, at least. I'm reasonably sure the problem still exists in Excel 2016. (Can someone confirm?)
Last edited by joeu2004; 10-20-2018 at 01:33 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks