Created attachment 161707 [details] Simple small anonymised demonstration sheet A simple colour scale on cell values ranging from zero to one appears to reverse the colours assigned to the 50th percentile and max. Therefore, where MAX should colour to Green it colours to yellow. I cannot tell whether the sheet structure has become damaged internally but I can confirm that other almost identical sheets [currently] function correctly. I have even "copied" a correctly functioning sheet and re-entered data for the desired subject but it still goes wrong. Enclosing a fully functional sheet where it can be seen that the conditional colour specification for column L should colour all the cells valued 1.00 as GREEN but they insist upon reproducing as YELLOW. Try selecting a block of 5 or MORE rows across any permutation of columns D to H and simply deleting the data. The colours then reproduce correctly. How interesting is that? I imagine you will have better facilities for tracing the impact of changes to these cells than I.
I should have mentioned that when I re-populated a copied correctly functioning sheet, the colour rendition initially worked correctly but started to go wrong as I finished populating Week 14 - Row 39.
(In reply to Colin from comment #0) > Enclosing a fully functional sheet where it can be seen that the conditional > colour specification for column L should colour all the cells valued 1.00 as > GREEN but they insist upon reproducing as YELLOW. > Try selecting a block of 5 or MORE rows across any permutation of columns D > to H and simply deleting the data. The colours then reproduce correctly. > How interesting is that? i can confirm the described behaviour with: Version: 4.4.7.2 Build-ID: f3153a8b245191196a4b6b9abd1d0da16eead600 Gebietsschema: de_DE Version: 5.4.7.2 (x64) Build-ID: c838ef25c16710f8838b1faec480ebba495259d0 CPU-Threads: 4; BS: Windows 6.19; UI-Render: Standard; Gebietsschema: de-DE (de_DE); Calc: single Version: 6.0.7.3 (x64) Build-ID: dc89aa7a9eabfd848af146d5086077aeed2ae4a5 CPU-Threads: 4; BS: Windows 10.0; UI-Render: Standard; Gebietsschema: de-DE (de_DE); Calc: Version: 6.4.4.2 (x64) Build-ID: 3d775be2011f3886db32dfd395a6a6d1ca2630ff CPU-Threads: 4; BS: Windows 10.0 Build 18363; UI-Render: Standard; VCL: win; Gebietsschema: de-DE (de_DE); UI-Sprache: de-DE Calc: Only values greater 1 in column L will colored green Saving attached file as *.xlsx and opening with excel 2016 will display green for values 1.
Experimenting further I have identified that if the minimum, mean and maximum are defined as real values (NOT min, percentile & max) of 0, .5 & 1 respectively then the colour scale is correctly rendered. This could be indicating that all three parameters must be set alike rather than a mixture as with the presented default - min, percentile, max.
This is not a bug. Using Percentile for intermediate value, you ask it for such a value that would represent exactly half of the population see [1] that refers to [2]. Having total of 46 values in the data set used in the conditional format, and 26 of them being 1, there's no value that would represent half of the population (23 values). So it necessarily ends up with 1 as the percentile value, thus being equal to max, and so effectively being equal to 2-color scale, as the third color is never used. Possibly what was wanted is using *Percent*, not *Percentile*. [1] https://help.libreoffice.org/6.4/en-US/text/scalc/01/05120000.html [2] https://wiki.documentfoundation.org/Faq/Calc/142
(In reply to Mike Kaganski from comment #4) > This is not a bug. Using Percentile for intermediate value, you ask it for > such a value that would represent exactly half of the population see [1] > that refers to [2]. Having total of 46 values in the data set used in the > conditional format, and 26 of them being 1, there's no value that would > represent half of the population (23 values). So it necessarily ends up with > 1 as the percentile value, thus being equal to max, and so effectively being > equal to 2-color scale, as the third color is never used. > > Possibly what was wanted is using *Percent*, not *Percentile*. > > [1] https://help.libreoffice.org/6.4/en-US/text/scalc/01/05120000.html > [2] https://wiki.documentfoundation.org/Faq/Calc/142 I think I have to disagree. I have other spreadsheets that also have the same scale of "fixed" percentage results 20,40,60,80,100 and they don't fail because there is no 50%. It's not actually possible to divide a week by a whole number of days to get 50% and I am only targeting the five weekdays which also imposes the same impossibility. Also, the remedy as mentioned in my comment #3, whilst setting the mean to "real" 0.5 never encounters a calculated 0.5 but gives a "bug free" result. Why does deleting any five random rows that also don't contain any 50% values obviate the bug? I'm not convinced the colour scale is a standard deviation function.
(In reply to Colin from comment #5) > (In reply to Mike Kaganski from comment #4) > > This is not a bug. Using Percentile for intermediate value, you ask it for > > such a value that would represent exactly half of the population see [1] > > that refers to [2]. Having total of 46 values in the data set used in the > > conditional format, and 26 of them being 1, there's no value that would > > represent half of the population (23 values). So it necessarily ends up with > > 1 as the percentile value, thus being equal to max, and so effectively being > > equal to 2-color scale, as the third color is never used. > > > > Possibly what was wanted is using *Percent*, not *Percentile*. > > > > [1] https://help.libreoffice.org/6.4/en-US/text/scalc/01/05120000.html > > [2] https://wiki.documentfoundation.org/Faq/Calc/142 > > I think I have to disagree. I have other spreadsheets that also have the > same scale of "fixed" percentage results 20,40,60,80,100 and they don't fail > because there is no 50%. It's not actually possible to divide a week by a > whole number of days to get 50% and I am only targeting the five weekdays > which also imposes the same impossibility. Also, the remedy as mentioned in > my comment #3, whilst setting the mean to "real" 0.5 never encounters a > calculated 0.5 but gives a "bug free" result. > Why does deleting any five random rows that also don't contain any 50% > values obviate the bug? > I'm not convinced the colour scale is a standard deviation function. My apologies, it appears I do have one cell containing 0.5 but the exercise of randomly removing 5 rows is unaffected by the inclusion or exclusion of the source data and therefore the value of this cell.
(In reply to Colin from comment #5) You need to understand what "percentile" means. Its not "(max + min)/2"; it's "value below which a given percentage of observations in a group of observations falls" [1]. So if you have a data set: 1 2 3 8 8 10 10 10 10 10 then your 10th percentile is >=1 and <2 (because 10% of cases - i.e. 1 of 10 - is below 1); your 40th percentile is 8 - because first 4 cases - i.e. 40% - are <=8, and the rest is >=8; and your 50th percentile is >=8 and <= 10. Your 60% percentile is 10, and same are all further percentiles. [1] https://en.wikipedia.org/wiki/Percentile
Created attachment 161713 [details] Settings for colourscale and result In response to comment #7
(In reply to Mike Kaganski from comment #7) > (In reply to Colin from comment #5) > > You need to understand what "percentile" means. Its not "(max + min)/2"; > it's "value below which a given percentage of observations in a group of > observations falls" [1]. So if you have a data set: > > 1 2 3 8 8 10 10 10 10 10 > > then your 10th percentile is >=1 and <2 (because 10% of cases - i.e. 1 of 10 > - is below 1); your 40th percentile is 8 - because first 4 cases - i.e. 40% > - are <=8, and the rest is >=8; and your 50th percentile is >=8 and <= 10. > Your 60% percentile is 10, and same are all further percentiles. > > [1] https://en.wikipedia.org/wiki/Percentile Attached image clearly demonstrates that it sometimes works. Sometimes = bug
(In reply to Colin from comment #9) > Attached image clearly demonstrates that it sometimes works. Sometimes = bug Sigh. It works *always*. Just your percentile may *correctly* give a value equal to max. Then any value in your data is no larger than medium value, so no value would be colored with max color.
(In reply to Mike Kaganski from comment #10) > (In reply to Colin from comment #9) > > Attached image clearly demonstrates that it sometimes works. Sometimes = bug > > Sigh. It works *always*. Just your percentile may *correctly* give a value > equal to max. Then any value in your data is no larger than medium value, so > no value would be colored with max color. Damn. Looking at that I now see how previous results looked just as I expected. I guess I was just lucky - or is it the other one because I now have more sheets to rectify :). Thanks for the lesson - and the patience.