conditional formatting with formulas doesn't work

[ashley]

Hi All,
I have a problem with ‘conditional formatting’ when I want to put formulas (otherwise it works fine).
I have a spreadsheet with large amount of data. I select a column where I want ‘conditional formatting’ to be applied. I choose option to insert a formula and a color. Unfortunately, the result is that either all cells are colored or none of them. Sometimes random cells are colored.
I tried to insert formula many times, for example like this:
=MIN(G8:G107) or G8=MIN(G8:G107) or G8=MIN($G8:$G107) and the results were not correct.

I am not sure what kind of mistake it is. Does the formula has to be written differently than formula in the spreadsheet? Could you please provide an example of correct formula for ‘conditional formatting’?

---

some more details of what I want to achieve.
I want to put CF with formula, which will be aplicable for one column. I want to use function Minimum in order to highlight the smallest value in the column and Maximum to show the biggest value. Therefore I expect Calc to find these values and colour them (I mean one ‘min’ and one ‘max’).

Recently I was trying to do it like this: ‘Create a new condition’ → ‘Formula is’ → MIN

not by ‘Create a new condition’ → ‘Cell value is’ → ‘equal to’

Is it a wrong way of thinking?

example below.

[Lupp]

If you want to highlight the cell where the minimum of the values from B2 through B101 occurs, you need to apply the comparison to just this range independent of the row-position of the cell for which the comparison is made. But if you compare the value of B10 to the MIN(B2:B101), and look at the formula from B11, you will see MIN(B3:B102). That’s the way relative addtressing works. Any cell in B compares to the minimum value of all the cells in B from a fix distance above to a fix istance below. This refers to different ranges for different cells. If you want a fix range, not only by length, but alo by position, you need to address it te absolute way.

[Lupp]

Formulae for CF work exactly as formulae in cells with the one difference that they don’t return a value to a cell. This difference may be the reason for which CF formuale not are starting with the “=”.
However, it would be useful if you told what you actually want to achieve, and which range you want to define a CF for.

Thus I need to guess: You want CF for the range G8:G107 where all the cells having the minimum value occurring in that range shall have specific attributes overlayn?
The formula G8=MIN($G8:$G107) unnecessarily addresses the column absolute then while the needed absolute row addressing is mssing. …

I prepared this example doing what you supposedly want in two different ways. For better overview it is reduced the range to 40 cells. Please refer to it to additionally clear the issue if needed.

Please rethink the concept of absolute vs relative addressing.
In the ‘Formula is’ mode of CF the chosen cell style will be overlayn if the result is different from 0 (zero).

[ashley]

hi, thank you for response and the example. could you explain why the rows need to be blocked (not the columns)?
PS I added some more details, pls see above.

[Lupp]

If the CF applied to a range exceeding the column, but the condition only looked at columns independent of the location of the cell where the CF should efectuate, you would need to use absolute column addressing.
In the actuals case every cell the CF shall apply to must be compared to always the same range from the second to the hundredandseventh row (most likely). Therefore the row range shall not be allowed to move with cell for which the formula needs to be evaluated. >> Absolute row addressing needed.
There are no other rules concerning the choice betwwen absolute and relative addressing but the one to get evaluated what you actually want. No sorcery!

[SM_Riga]

Hello @ashley

You need to pay attention what cell is selected with cursor, when setting conditional formatting (CF) rules. All formulas will be extended on other cells starting from cell selected. So if you use relative references (without $ sign), range G8:G107 will be changed relative to each of the cells where CF is applied. Here is an example of creating CF in my sample sheet :

Step 1. Select the range A2:A30 and pay attention which cell is selected by cursor (thick black rectangle), A2 in my example

Step 2. Go to Format -> Conditional Formatting -> Condition...Create a new condition Cell value is -> equal to -> MIN($A$2:$A$30) and apply the style needed. Pay attention that range $A$2:$A$30 addressing is absolute, so it will not change, when Calc will iterate over all cells selected and apply CF for each cell. It is the same, like when filling down column with formulas.

Step3. Here comes the result. Every cell that matches MIN($A$2:$A$30) formula result will be highlighted.

[mikekaganski]

1. Conditional formatting includes special options exactly for that: Cell value is→top/bottom N elements; enter 1 to get one topmost or 1 bottommost element from the CF range highlighted.
2. But if you use formula, you need to know that formulas in CF work just as in cells (just as @Lupp said), i.e. usual rules about relative vs Absolute addresses apply. Your formula, B2=MIN(B2:B101), uses relative addressing in all components, meaning “this cell is minimum of range starting from this cell, and going down 100 cells”. The range moves with the cell. This is what @SM_Riga explained in great detail, with images.

What you likely need is B2=MIN(B$2:B$101).

[ashley]

thanks, however in my first post I wrote that I already tried formatting with absolute adressing of column, like this: B2=MIN(B$2:B$101)
but Calc shows three cells not only one minimum value… so this didn’t work

[mikekaganski]

Oh! Lupp told you the error in your “absolute” addressing. Note the difference between $ placement.

[mikekaganski]

See this QA for another explanation of addressing.