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]

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)

[LeroyG]

@DavidGDFC

space between paragraphs

In comments, they collapse.

[Lupp]

See the attached example including the explaining text therein.
disask_130522_complicatedConditionalFormatting.ods (43.3 KB)
Trying to include the complete “logic” of CF in its definition leads to hardly editable and maintainable results.
BTW: Formats can’t be superposed or “added” or the like. CellStyles inherit from one parent, and only CF can use them for the view without actually assigning them.

[DavidGDFC]

Thank you for your help. It’s much more complex than I’m capable of doing in Calc, but I’ll try to spend time on it and see if I’m able to understand it completely.

[koyotak]

What’s so difficult about it? 3 cases with 3 simple conditions, see section [F38:N50]:
disask_130522_complicatedConditionalFormatting_v02_085310.ods (45.9 KB)

[Lupp]

Do you feel sure you understood the question?

[DavidGDFC]

Thanks, but my issue was about applying two properties at the same time on the same cell: red background and a particular number format.