In 'Conditional formatting' in Calc, I can't apply two conditions on the same cell

[DavidGDFC]

I need to apply the following conditional formatting rules to my cells:

1. If the value is less than B10, I set the background colour to red.

2. If it’s a maximum value (‘is in top N elements’), I apply this number format: "▲ "0,00;"▲ -"0,00

3. If it’s a minimum value (‘is in bottom N elements’), I apply this number format: "▼ "0,00;"▼ -"0,00

To apply each property, I have already defined three styles based on the default style:

1. On ‘red background’, I set the background colour to red and I don’t touch anything else.

2. On ‘maximum’, I change the number format and I don’t touch anything else.

3. On ‘minimum’, I change the number format and I don’t touch anything else.

The way I’ve defined things, a cell could have a value less than B10 and, at the same time, reach a maximum or minimum value, so two conditions could be met at the same time and, therefore, two styles should be applied. I understand that if two styles modify the same property (for example, background colour), only one background colour can be applied to the same cell, but I was hoping that two styles could be applied in my particular case as they change different properties.

I’d like to ask if what I want do is achievable, without using macros or doing anything too complicated. I’ve been trying different approches and I’ve been doing some research online, but I ran out of ideas.

Any feedback would be appreciated. Thanks in advance.

[LeroyG]

Create two new styles, one for values lesser than B10 that are in the top N elements, and another for values lesser than B10 that are in the bottom N elements.

[DavidGDFC]

(post deleted by author)

[DavidGDFC]

I thought about that. The problem is that there’s no apparent way to say:

if condition 1 and condition 2, then style 1

If instead of selecting ‘Cell value’, you select 'Formula is, I think you can establish multiple conditions, but I’m lost here.

It would be great if you could add a second condition to a previously-defined condition, but clicking on ‘Add’ just creates a new condition independent from the others.

(For some reason, I can’t put any space between paragraphs any more. This is my first time in Ask and I’m still figuring out things.)

[DavidGDFC]

Here’s my document, by the way:

doc.ods (41.3 KB)