Forum Discussion
Solved
Can anyone explain this behaviour?
Hi Experts,
I've been using the BITOR function in a formula - but the weird thing is that it sometimes gives me a #NUM! error when processing two digits where one of the digits is calculated.
So here's the scenario:
If I put the following calculation in a cell, I get the expected result - i.e 49
=BITOR(1,49)
But if I calculate the 49 value using a double inverse, i.e.
=BITOR(1,1/1/49)
I get a #NUM! error - which, given the documentation says:
- If either argument is greater than (2^48)-1, BITOR returns the #NUM! error value.
I guess it takes more than 48 bits to store, but I'm too lazy to check that maths.
BUT what I don't understand is that if I have another cell, say C1 with the formula
=1/1/49
which give the result of 49 (of course), and then refer to cell C1 in my formula, I still get the #NUM! error!
=BITOR(1,C1)
Even if I split the calculation over two cells, say C2 and C1, so C2 is
=1/49
and C1 is
=1/C2
The error still occurs.
Can anyone explain why BITOR does not work when one of the digits is calculated in the above scenario?
BACKGROUND
I was actually trying to replace zeros with -1 in a table calculation using something like
=IFERROR(BITOR(1,C1),-1)
where C1 contained the calculation in question. I got around the problem using an IF statement, but I am kind of disappointed that the 1/1/x trick did not work - so I'm not seeking an ANSWER to the problem I had, just an EXPLANATION of why the calculated value won't work in the formula, especially when two steps were involved and the calculated cell (C1) clearly had a value of 48.
The illustration below shows the problem - rows 4-46 hidden for brevity
You can see the problem in row 50
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. 😂 😀
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.
I guess float point error
=BITOR(1, INT(1/(1/49)) )works
