1

I have the following data sheet

E F G H I
1 2 3 4 5
6 7 8 9 0
0.1 0.2 0.3 0.4 0.5
0.6 0.7 0.9 0.8 0.1

I would like to implement a conditional formatting formula that would:

  • bold the maximum number of each row (i.e. I1, H2, I3, G4 would be bold);
  • bold+red the maximum number of each block, where a block consists of multiple rows - E1:I2 is one block, E3:I4 is another, etc. (i.e. H2 and G4 would be red + bold).

The first rule is easy to implement. I simply apply the condition =E1=MAX($E1:$I1) on the entire range E:I.

For the second rule, what I currently have is the condition =E1=MAX($E$1:$I$2) on the range E:I, however this only works for the first block. One way to solve it is to apply it to the range E1:I2 instead, then copy the same formula to the range E3:I4, manually fix the row numbers in the condition, and repeat for every block below. However, there are quite a few blocks below, so this solution isn't ideal. Is there a better way of implementing this blockwise formatting?

EDIT

A note on @doubleunary's accepted answer below - the answer only works for blocks of size 2 (i.e. 2 rows). For larger blocks (in my case of size 6) I had to use the following, more general formula (fortunately none of my cells are empty so I didn't have to worry about that part):

=E1=MAX(OFFSET($E1:$I6, -MOD(ROW(E1) - 1, 6), 0))
1

1 Answer 1

2

Use offset(), row() and iseven(), like this:

=isnumber(E1) * ( E1 = max(offset($E1:$I2, -iseven(row(E1)), 0)) )

The isnumber() test prevents the conditional format from highlighting blank rows.

3
  • excellent :) . However, you must add a filter for the cells of the range that are empty, like: =and (E1=max(offset ($E1:$I2, -iseven (row (E1))), 0)), E1<>"")
    – Daniele
    Commented Oct 13, 2022 at 15:16
  • Thanks. Edited the answer. Commented Oct 13, 2022 at 15:59
  • Thanks for this. Offset was indeed the key idea here, though I had to adapt this answer a little bit for my use-case (since in my actual, much larger datasheet, the blocks were actually bigger than 2 rows). I'm accepting this answer but editing my final solution into the question for posterity. Commented Oct 13, 2022 at 22:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.