[Libreoffice-bugs] [Bug 143610] New: Three Colour Conditional Colour Formatting can't be forced to ignore zero

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=143610

            Bug ID: 143610
           Summary: Three Colour Conditional Colour Formatting can't be
                    forced to ignore zero
           Product: LibreOffice
           Version: 7.0.6.2 release
          Hardware: All
                OS: Windows (All)
            Status: UNCONFIRMED
          Severity: normal
          Priority: medium
         Component: Calc
          Assignee: libreoffice-bugs at lists.freedesktop.org
          Reporter: that.man.colin at gmail.com

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

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20210729/0bea34b7/attachment.htm>