0

I have a large data file where i want to filter between the values of my first column, which is values of time (in seconds). This column goes from 1 to x number, and I want to have to conditional formatting to change the color of the cells of my interest...

The fist thing of my interest is to select was the cells that are a multiple of 1200, and I managed to do it by selecting conditional formatting > formula > =MOD($A1,1200)=0, so far so good.

However... the second thing I want to format on the time column, is the 5th cell below every cell that is a multiple of 1200. These cells of my interest also are also conveniently located alwats at the multiple of 1200+0.1. So at 1200.1,2400.1,3600.1, etc.

So what i was thinking of doing to create the conditional was the OFFSET function and pass as the reference the MOD function described above, however if i write it like this it has an error because the MOD function has to take in a range of cells as a reference, not the result of an operation (in this case the remainder of a division).

Can someone please help me with how to select the cells in the column that are a multiple of 1200+0.1, or that are located 5 rows below the cells that are a multiple of 1200? Thanks

Im CS
  • 3
  • 1

1 Answers1

0

You can apply the same format condition twice. The first rule is exactly what you're doing, which works:

Formula: =MOD($A1, 1200)=0
Applies to: =$A$1:$A$100

Use exactly the same condition for the second rule, but stagger it so it applies 5 rows below the first (notice that A6 is conditional on the value in A1, and so on down column A):

Formula: =MOD($A1, 1200)=0
Applies to: =$A$6:$A$100

You might have to play with the "Applies to" ranges a bit, but this should get you going.

enter image description here

RichardCook
  • 846
  • 2
  • 10