How to mark the min and the max values in an Open Office Calc column?

Question

I have a table like this:

| A | B |
|---|---|
| a | 5 | <- max, should be red
| b | 1 | <- min, should be green
| c | 0 | <- zero, should not count
| d | 1 | <- min, should be green
| e | 3 |
| f | 5 | <- max, should be red
| g | 4 |
| h | 0 | <- zero, should not count

The objective is to get the maximum values formatted red and the minimum values green. The cells with the value 0 should not count (as minimum value).

I tried conditional formatting with following rules:

Condition 1 Formula is: MAX(E2:E40) Cell Style: max

Condition 2 Formula is: MINIFS(E2:E40;E2:E40;">0") Cell Style: max

But the result is, that all cells with value > 0 get marked red.

How to mark the greatest and the lowest values in a column and ignore the cells with a defined value?

Answer 1

The trick with conditional formatting is that the current cell is referenced by the first cell, not by a range of cells. That is, E2 refers to the current cell, applying to E3, E4 and so on throughout the conditionally formatted range.

References in formulas change for each cell unless they are fixed with $, so in the formula below, $E2 is used to fix the reference to column E (because the value is in column E even when we're formatting column D) but lets the reference to row 2 change for each row that needs to be formatted. In contrast, the range to check for min and max values should not change no matter what the current cell, so that is $E$2:$E$40.

Anyway, whether you followed that explanation or not, here are the two formulas.

$E2 = MAX($E$2:$E$40)
$E2 = MINIFS($E$2:$E$40;$E$2:$E$40;">0")