Bug 69799

Created attachment 87735 [details]
Approximation exemple

Sorry, misplaced "Submit" action... Ignore the comment 1 except for the attachment.

Potential explanation : if the numbers in columns B to U are the results of a computation, they might be "approximatly" (but not "strictly") equals to the displayed values, which are governed by the number format and/or the column width.

See the attachment 87735 [details] : if B3 is "0.08000001" instead of "0.08", all is explained, and the conditional formatting is exonerated.

Dear Dominique, I agree with your explanation but we stil have a problem - the conditional formatting in reality is unusable now.
There is a need for solution, because now 2 "equal" numbers are justified as not equal even these looks like are equal.
I believe there is some thing wrong in the way the conditional formatting is working or may be in the way the numbers are represented.

As you mentionned initially, the "round" function solve that issue : that's exactly what it's done for, and it's well-founded to use it broadly

Please read http://en.wikipedia.org/wiki/Floating_point#Accuracy_problems and try to understand floating point numbers and lose of precision through operations on them.

I believe it is not so important for me to read about and try to understand floating point numbers. 
This will be more important for the regular people who may need to use this functionality in future and may found same problems doing so.
Writing about a need of solution I was thinking about the users who will be not capable or not willing to understood things on this level.
May be this problem need to be solved by changing some thing in the help system and in the documentation of the Libre Office? 
This way may be possible to keep the regular users informed and know how to do conditional formatting in such cases.

To confirm that the floating point calculation is part of the problem, I:

A_ “played” with the specific numbers that are part of the data that sum up 
always exactly “=1”, and;
B_ multiplied the numbers and the user-defined format by 100.

For case “A”, depending on the specific numbers, the color of the result would 
change, while I know it was always summed up exactly “=1”.

For case “B”, whichever values I used (summing up “=100”), the color of the 
result was consistent.


After some experiments confirming the original report, I believe there are 3 
different matters here.

1_ Floating point calculations. As a user, I would expect that using 2 (or 3, 
or “a few”) decimals should not trigger any inconsistency in the real 
calculation. I am not talking about how many decimals are displayed (thus, 
rounding the actual result accordingly). I think that using 2 decimal places is 
very common and the results (whether from a formula or from the user-defined 
format code) should be consistent, and not depending on the specific data 
values. Using 2 decimals in source data values and displaying 2 decimals, is 
not the same as using 14 decimals or using some relevant scientific notation.

In other words, a user inserting values with 2 decimal places and using format 
codes with 2 decimal places is not likely to think “I should explicitly add a 
round function up to 2 decimal places, just in case”. It would be an 
unrealistic expectation from common users, IMHO.


2_ The user-defined format seems to have some limitation after its second 
condition. I can use “[=1]” (or “[=100]”, or any other condition) if it is part 
of the first or the second section of the user-defined format code, but in the 
third section Calc rejects the usage of conditions. It can still accept a color 
though.

At first glance, this seems to be in contrast to the help in Calc, where 3 
conditions in three sections are used as example (quote: “All temperatures 
below zero are blue, temperatures between 0 and 30 °C are black, and 
temperatures higher than 30 °C are red.”). But it should be noted that the 
user-defined code in the example (in the help) only uses 2 sections with 
conditions, leaving the third section without a conditional range of results 
(“[BLACK]#,0 "°C"”).

In other words, the third section is applied to whichever range (condition) is 
not covered by the first 2 sections.

Perhaps this limitation (only the first 2 sections can include “conditional 
brackets”) should be explicitly added to the help example in Calc?


3_ According to my experiments, the floating point calculations seem to affect 
the user-defined format code, but not “Format -> Conditional Formatting”).

I used different cells with the same “=sum” formula, without user-defined 
format. In these cells, I used “Format ->  Conditional Formatting” instead, 
with the same conditions that were used in the original “=sum” cells. While the 
original summing cells indeed show different colors for the same exact “=1” 
result, the new cells are consistently showing “1” in green.


I would expect for the user-defined “conditional brackets” to behave in the 
same way.

Thank you and Best Regards,
Ady.

Closing as NOTABUG - this is a floating point thing which is well documented (the fact that you didn't want to read about it . . . well, the documentation is there). Markus and Kohei (both of our top experts in Calc) have explicitly said these are not bugs and will not be "fixed", it's a consequence of floating points. There are a few other reports on this and you are more than welcome to go and read all of the lengthy discussion about why it's not a bug and will not be fixed.