https://bugs.documentfoundation.org/show_bug.cgi?id=168673
--- Comment #9 from Mike Kaganski <[email protected]> --- (In reply to Dominik Stadler from comment #8) > With "two ways" I meant that the cell is compared with itself as part of > COUNTIFS() and somehow this comparison ends up being "not equal", which > indicates that the value is handled in two different ways, otherwise > "precision" or "conversion to text" couldn't have an impact. When you use a comparison like '=A11=A10', the comparison doesn't use any conversion to strings. But when you use COUNTIFS with a Criterion like "="&A11, that criterion is a string - and A11 gets converted to string automatically there. The resulting criterion is a string "=0,00100081018518519", which has a string which is *not* a precise representation of the value in A11, but rounded to 15 significands. The *precise* criterion would be "=0,001000810185185185", but there is no method in Calc to convert the number to string with that precision. Then the criterion is evaluated, and the string "0,00100081018518519" gets converted to number again - to perform comparison to the actual cell value (numerical). Since cell A11 contains 0,001000810185185185, and the string "0,00100081018518519" gets converted to number 0,00100081018518519 - their comparison gives false. Of course, in this specific case, it's unreasonable to use the string as criterion: you could rewrite your formula to be simply =COUNTIFS($A$7:$A$12; $A11) and avoid the problem completely: if you only need an equality, it's better, more robust, and also faster to avoid that "convert to string, then convert back to number" dance. But if you would need a criterion like "<>"&$A11, that problem would appear again, so that is not a solution to this bug. -- You are receiving this mail because: You are the assignee for the bug.
