cell conditional formatting based on percentage - calc

[fustre]

Hello,

Can you please tell me if it is possible to use condtional formating in calc (LibreOffice Portable win 10) to change the amount of fill colour in a cell according to the percentage entered in it?

Cell % value or number = 1 % - the cell is fillled with 1% colour (any chosen single colour)
Cell % value or number = 50 % - the cell is fillled with 50% colour (any chosen single colour) and so forth till 100% coloured if the cell value = 100% / 100.

I have the cell changing colours according to conditions - cell value in between 1 to 50 = red, 51 to 70 = yellow, 71 to 99 = green and 100 = blue but it would be ideal and a lot more visually pleasing if the colour fill changed depending on the value in the cell.

Thanks in advance for any assistance.

[anon73440385]

Could you tell us your notion of 1% red, 10% red or 50% red - I got no clue what this should mean (Hopefully you don’t mean “10% red” should mean: 10% of the background on the left side of the specific cell is “red” and 90% to the right size of the cell is still white - If that’s your notion: This is not possible). - see @Lupp’s answer.

May be you are looking for Color Scale (2 Entries) and selecting a color for Minimum (e.g cyan) and a a similar color for Maximum (e.g. dark blue) and you’ll get a smooth transition from Min color to Max color depending on the cells values.

[Lupp]

For rather recent versions of LibreOffice Calc there were implemented some toys under the topic ‘Conditional Formatting’ which actually break the concept.

I would omit them as an MSiform nonsense, but you may look at the attached examples.
conditionalToys.ods

(The examples are treaded under the ‘All Cells’ mode of CF despite the fact that this title is misleading in the presenteed cases.)

[Edit1]
I changed the number format of the example to 0.00%.
If you wanted to have colouring using an ‘All Cells’-mode CF showing roughly statistical facts concerning the sample, please state this explicitly.
[/Edit1]

[fustre]

Oooops, I wrote my reply to Opaque too soon.

Lupp , thank you so much, the Databar examples are very much what I was looking for specially the first examplle (column D) with the use of a gradient

I also like that you can enter the percentage number directly into the cell as opposed to referring to another cell as in the example.

I was going to ask you where do you change the gradient colour but found it under More Otions > Bar Colours > Positive.

Fantasctic - Lupp I am very grateful to you for solving my problem and I am sure helping many others too with something that was thought impossible to do.

Cheers

[Lupp]

Well, thanks in return, though I am not happy with the tool. The CF feature was a clean and efficient tool for many years in StarOffice, OOo, AOO, and LibO based in a transparent way on cell styles. The new toys subsumed under CF break the concept, and surely needed a lot of hacking-the-code to be implemented. It’s the “Do As MS Do” approach which may deviate LibO from being better, and reducing it to just being a bit more compatible. Compatibility with MS software is impossible without disregarding quality and/or clarity.

[fustre]

That unfortunately is the cost of competition and progress but then again I do recall that not so long ago that CF was limited to only three conditions and quite confusing (to me at least).It took me ages to work out how to highlight a row based on a cell’s value.

If it extends the tools and features that enable easier use for users, for them to be more productive, effective and creative then it can’t all be bad despite breaking away from the original concept. The sad fact is that evolution occurs everywhere whether we like it or not.

It all comes down to innovation and I look forward to the time when the OOos development markedly distinguishes it from MS, leads the way and forces MS to be compatible with them rather than the other way around. Then hopefully not only us impoverished users, those who harbour an aversion to monopolistic corporations but will open software alternatives gain popularity acceptance across a broader range of users. Fear of loss of profits is what MS needs.

[Lupp]

Disagree.
The “Big Players”, in specific, aren’t interested in progress, but in earnings and power. They don’t try to lead an honest competition, but to close-in their customers (read: slaves) by marketing tricks a free competitor hasn’t at hand. The once reached de-facto dominance is the major if not only means to force others to be “compatibel” which can’t be achieved without endangering clearer and better concepts.

BTW: The former no-more-than-three-conditions concerning CF never was due to internal limitations, but accepted for the UI only.

The basic need concerning general-purpose software for office application is long-term stability of an open file specification. The only such specification meeting the needs currently available is ODF. MS once announced support for it, but, of course, never implemented it sufficiently.

Again MS Office needn’t be good. It needs to be “attractive”.

[fustre]

Hello Opaque,

Thanks for your prompt reply. Yes unfortunately I did mean 1% red and the rest white in the same cell.

Will try Color Scale and play around with it but it looks like it lacks the styles formatting options available with the normal condiion.

Pity that the standard colour swatch does not have a gradient fill option which on reflection I think I was half getting at in my question (like those in graphic programs).

[Lupp]

Please don’t use The ‘Add Answer’ for comments (including answers to questions posted in comments by others.)