Hey guys!
I'm quite new here (registered 8 minutes ago) and have discovered a weird situation in Excel.
I'm using Excel 2013 and entering this function gives me weird result:
That results in 0.0001
Please Login or Register to view this content.
Evaluating the function reveals that it results in 0.0000999999999...
I know this is beacause of the way Excel handles numbers in general, but my question is what would be the best way around this particular situation.
Thank you in advance!
Last edited by Marchack; 11-22-2014 at 06:39 AM. Reason: Solved
Yes - the problem is caused by the way Excel counts numbers (binary floating point). Best workaround is to multiply by 10,000, do the mod and divide by 10,000 again
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
That works, thanks!
Is there any hope this might be fixed someday?
I doubt it!! This is Microsoft we're talking about...
But be sure the results are integers. In an Excel file, Glenn wrote: =MOD(10000*A1,10000*B1)/10000. That worked with B1=0.0025. It does not work with B1=0.0024.Originally Posted by Glenn Kennedy
The following does work: =MOD(ROUND(A1*10000,0),ROUND(B1*10000,0))/10000.
The problem is: most non-integers cannot be represented exactly in binary; so A1*10000 or B1*10000 might not result in an integer. Compounding confusion: Excel arbitrarily formats only the first 15 significant digits. So 0.0024*10000 looks like 24.0000000000000, but it is actually 23.9999999999999,96447286321199499070644378662109375.
(I use period for the decimal point and comma to demarcate 15 significant digits.)
Fair point...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks