Ranges in conditional formatting

[jt1122]

I’m trying to understand how ranges work in conditional formatting.
Here is a MWE:

I’d like to colour the rows in column B having a value of 0.

If I enter the conditional formatting formula: $B1=0 on the range B2:C5 I get this:

But if I enter $B2=0 on the same range I get this:

which is the desired outcome.

Why is that?. From what I understand relative indices are measured based on the defined range, where 1 for the row indicates the current row the formula is evaluated on. So shouldn’t $B1=0 be the correct rule?.

Thanks

[mikekaganski]

@jt1122

From what I understand relative indices are measured based on the defined range, where 1 for the row indicates the current row the formula is evaluated on.

No, relative indices are measured relative to the top left cell of the range, with that cell’s indices being the “base”: if you have a base cell B2, then “same row” means “row 2” means using 2 in the formula, and “same column” means “column B” means using B in formula. If it would be “your way”, then you would be unable to define a condition like “a cell above”.

[jt1122]

Thanks but the above answers seem to contradict this: https://shrtn.escalar.pt/1fRM.
So in the conditional formatting formula the “current row/column” are simply those of the top-left cell of the range?.

[Villeroy]

Apply cell style “Bad” if the cell value in absolute column C, of the same row relatively to the top-left cell equals zero.

[mikekaganski]

@jt1122

but the above answers seem to contradict this: …

1. Why do you create an obfuscated link?
2. Why should we care if a random Stack Overflow answer contradicts?
3. You don’t explain what specifically contradicts.

[koyotak]

@jt1122

$B1=0 on the range B2:C5

Logically, it’s quite simple: You query whether the cell above is “0”! So: B2 = ($B1 = 0); B3 = ($B2 = 0); etc., if true than red background. The empty cell is accepted as “0”.