Description: See also 74967 last mod 2018-07-01 - I believe this is related but appears to reference the simple single conditional colour formatting. As some consideration should also be given to the three colour conditional formatting I have filed a new bug. It's undoubtedly preferable to mark it as a dupe than to fail to consider it. You will appreciate that the colours can be defined for Min Middle Max thereby producing a graduated colour scale. Generally, the use of a pseudo traffic light scheme of Red, Yellow & Green produces representative and relative scaling for the variation in numerical ranges. Let's assume 1 - 10 with min = green mid = yellow and max = red I have experienced the issue where calculations result in zero and it would be preferable for that not to create a Min colour scale because the true minimum value would still be the lowest positive number, possibly 1, but also higher, so the colour scale should recognise whatever is the minimum number and colour scaling should commence with that number. It would never be an issue if negative numbers featured in the range because a zero would genuinely represent a logical position in the grand scheme of things. The solution should simply be to define the Min value as 1 - which all the calculations are likely to transit on their way to Zero. For the record, attempting to set the min value as either a value of 1 or a formula =1 has no impact - the min value colour is still assigned to a zero value. Attached is a simple example where a number of cells have transited through 1 to zero - it is no longer a sheet of formulae, I simply pasted the values from a functioning sheet. And yes, I am aware that some columns consider the 0 as a red cell :)). I have ascertained that deleting the zeros yo produce an empty cell "" does then transfer the burden of Min onto the genuine Min value in the data. Obviously, the workaround is to test the formula for zero and substitute "" for the calculated value as appropriate. Surely the conditional format management should recognise a number or formula representing 1. The background colour of all cells can be set to a nice neutral grey, which would be visually acceptable when the cell should not have been assigned a nice bright colour. Steps to Reproduce: With the attached sample, amend the conditional colour formatting for any column where the cell has no value (zeros are display suppressed)in an endeavour to remove the background colour I have tried min = number entering 1 I have tried min = formula entering =1 I have physically deleted the zeros which then results in min being identified as 1 and producing the correct colour rendition. This also proves that an excluding formula to ensure null cells would achieve the desired/anticipated/logical effect. Actual Results: Too much of the wrong colour Expected Results: only cells with real positive values to be colour formatted Reproducible: Always User Profile Reset: No OpenGL enabled: Yes Additional Info: Version: 7.0.6.2 (x64) Build ID: 144abb84a525d8e30c9dbbefa69cbbf2d8d4ae3b CPU threads: 4; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win Locale: sv-SE (en_GB); UI: en-GB Calc: threaded
Created attachment 173955 [details] Sample .ods Please note that this is no longer a functional sheet, merely an array of "paste special" values with colour scale conditional formatting.
(In reply to Colin from comment #0) > Steps to Reproduce: > With the attached sample, amend the conditional colour formatting for any > column where the cell has no value (zeros are display suppressed)in an > endeavour to remove the background colour > I have tried min = number entering 1 > I have tried min = formula entering =1 > > I have physically deleted the zeros which then results in min being > identified as 1 and producing the correct colour rendition. This also proves > that an excluding formula to ensure null cells would achieve the > desired/anticipated/logical effect. Can you give very simple and clear steps on what I should do with your document? Set to NEEDINFO. Change back to UNCONFIRMED after you have provided the steps.
Focus H5 Observe contents = 0 colour = Green Enter 1 observe colour = Orange Focus H5 Observe contents = 1 Enter 0 Observe contents = 0 colour = Green Focus H5 Delete Observe Contents = null colour = White - null Menu>Format>Conditional>Manage EDIT I4:I24 Experiment with minimum = min and with minimum = value 1 or = formula =1 Focus H5 Experiment with the 0 that exists Experiment with EDIT = 1 Experiment with delete = null - "" You will notice that White Green and Orange are the featured background colours but if the minimum is set to 1 then all the "ones" in column I should go Green (Not Orange) and the 0s - (think blanks or nulls) - should presumably go White as they are below the minimum value. Does that make sense?
(In reply to Colin from comment #3) > Does that make sense? Perhaps it would be more apparent if you delete contents (0s)of all the green cells making them all null then you should observe all the 1s change from Orange to Green - the true minimum
@Colin, I think this is happening because you have "Percentile" selected as the mid-range color. If "Percentile" is set to 50, this would take the MEDIAN number within column I, as defined here: https://help.libreoffice.org/latest/en-US/text/scalc/01/05120400.html?&DbPAR=CALC&System=WIN#colorscale The numbers you have within column I has a bunch of 0's, so currently the median value for the mid-range color is 0. So no matter what what you set as the Min, it the colors won't change. If you want to see the colors change to the way that you described in Comment 3, try changing the mid-range to "Percent" instead. I don't think this is a bug, so I'm going to close this ticket as NOTABUG. However, feel free to reopen the ticket and change the status back to UNCONFIRMED and explain the issue further. I tested this with latest stable build, 7.6.2.1, so you might want to update if you are still having problems. Version: 7.6.2.1 (X86_64) / LibreOffice Community Build ID: 56f7684011345957bbf33a7ee678afaf4d2ba333 CPU threads: 6; OS: Windows 10.0 Build 22621; UI render: Skia/Raster; VCL: win Locale: en-US (en_US); UI: en-US Calc: CL threaded
(In reply to Kira Tubo from comment #5) > > I tested this with latest stable > build, 7.6.2.1, so you might want to update if you are still having > problems. > > Version: 7.6.2.1 (X86_64) / LibreOffice Community > Build ID: 56f7684011345957bbf33a7ee678afaf4d2ba333 > CPU threads: 6; OS: Windows 10.0 Build 22621; UI render: Skia/Raster; VCL: > win > Locale: en-US (en_US); UI: en-US > Calc: CL threaded I'm not convinced 7.6 is really the stable build line, isn't it - Version: 7.5.7.1 (X86_64) / LibreOffice Community Build ID: 47eb0cf7efbacdee9b19ae25d6752381ede23126 CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win Locale: sv-SE (en_GB); UI: en-GB Calc: threaded I tried what you suggested and observed that it made no change to the column selected for amendment - in fact, >Format>Conditional>Manage when selecting any single column now places a marquee around the column immediately to the left of the selected range and changes that format but still produces a colour rendition for the "wrong zeros" when they should be blank. Additionally perhaps you could try, manually selecting the contents of column J and right-clicking to select conditional format > change the colour of the minimum column to something outrageous and accept it. Observe. Now use the "undo last action button" on the toolbar. Are you getting the same "feature" as me? Is it worth us collaborating on identifying all the "features" now apparent with this report as they may possibly warrant multiple reports rather than "Conditional Formatting has some interesting features"? Perhaps you could verify whether your amending of any column also "marquees" the adjacent column. We may now have two "special features"
(In reply to Kira Tubo from comment #5) > MEDIAN number within column I, as defined here: Not being pedantic I is full of positive numbers but another feature is also apparent and it may be the cause of the H/I swap. Before you start any testing observe the values in Format>Conditional>Manage. Conduct some testing and undo the results and changes to the formatting with the "undo last action" button. Now observe the values in the management matrix. Where did all the new "sets" come from?
(In reply to Colin from comment #6) > I'm not convinced 7.6 is really the stable build line, isn't it - 7.6 and 7.5 are both stable at the moment. 7.5 just has more bug fix point releases.
I can confirm I have just clean installed Version: 7.6.2.1 (X86_64) / LibreOffice Community Build ID: 56f7684011345957bbf33a7ee678afaf4d2ba333 CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win Locale: en-US (en_SE); UI: en-US Calc: threaded On another computer so it's never had a profile to corrupt and still the colour formatting will not treat Zero as a "null" format and more significantly the error in the Conditional Format Management matrix is still occurring. That is to say; 1. Modify the format of H4:H24 either via the toolbar or the right-click marquee action 2. Observe that a toolbar management selection will place the marquee around the immediately preceding column data as opposed to that being selected in the matrix 3. Change the colour of the MIN value to anything noticeable 4. Accept the edited change - it's still not "nulling" zero 5. Undo the change with the toolbar "rotator" or a simple [Ctrl]+Z 6. Observe how the formatting does not return to its original status 7. Observe the corrupted first entry in the Conditional Format Management Matrix You will notice that I have now mixed and matched en-us en-uk and en_SE - Locale, language and UI.