Forum Discussion
Can anyone explain this behaviour?
In an empty workbook, I entered 49 in B1 and =1/(1/B1) in C1, then saved the workbook.
This is how these cells are stored in the XML representation of the worksheet:
As you see, the stored value of C1 is 49.000000000000007, not 49. However, since the 7 is the 17th digit, Excel cannot display it, even if you format C1 as Number with 20 decimal places.
But if I calculate the 49 value using a double inverse, i.e. =BITOR(1,1/1/49) [,] I get a #NUM! error
Expanding on Sergei's and Hans's correct responses, it might be noted that 1/1/49 (sic) is not the same as the "double inverse" 1/(1/49).
1/1/49 displays 0.0204081632653061 , whereas 1/(1/49) displays 49. Mathematically, 1/1/49 is equivalent to 1/(1*49).
Hans correctly explains that 1/(1/49) is not exactly 49, despite appearances. But even the 17-significant-digit value in the XML file is an approximation. In general, it is the minimum number of significant digits that is necessary and sufficient to reproduce the original binary value (*).
-----
* Re: 17 "is the minimum number of significant digits that is necessary and sufficient to reproduce the original binary value" .... If Excel did not normally truncate such input to 15 significant digits. (Klunk!) But we can input such numbers with no loss of precision by using Power Query or VBA.
JoeUser2004 Thank you for pointing out the sloppiness in my (lack of) parentheses. However, I did have the parentheses in the correct place in the SS. I'll put my sloppiness down to (a) being late at night and (b) having to rewrite the question 3 times because the webpage refused to accept my question while I had any tables or code in my answer. (like SergeiBaklan 's answer - HOW DID YOU MAKE THAT WORK?) Could not click Post until all my careful formatting had been removed.
I'll put my sloppiness down to (a) being late at night and (b) having to rewrite the question 3 times because the webpage refused to accept my question
Been there, done that. 😂 😀
