How to apply a relative reference in conditional formatting formula?

I have a whole bunch of cells (28 so far) that need conditional formatting.

Here is what the data looks like:

I want the colours in the top "Last Period" metric cells to vary depending on the values inside the three rows below: so if the top number is bigger than the grey number, it's light green; if also bigger than the blue number, the text goes yellow; and if also bigger than the purple number, the cell shoots confetti out of the screen and onto the user's lap. Likewise if it is lower it sends a negative signal(s), perhaps ultimately producing a foul sour milk smell, filling the user's office with shame.

Here is an example of one formula, which turns the cell background a nice shade of green when the percent difference between `D5` and `D6` is higher than 1.

`=D5/REGEXEXTRACT(D6,"\((.*)\)")*1>1`

It works—BUT (there's the "but"), I can't apply a proper cell range to this condition, because I really want to say, "if the cell being tested is bigger than the cell below it, turn green." Otherwise it's always testing against `D5` and `D6`.

From what I understand, the only way I could do it is to create conditional formatting for each and every of the dozens of cells... which is just not going to happen.

Would a script be better for this kind of feat?

Try this:

``````=INDIRECT(ADDRESS(ROW(),COLUMN(),4))/REGEXEXTRACT(INDIRECT(ADDRESS(ROW()+1,COLUMN(),4)),"\((.*)\)")*1>1
``````

1 The third parameter in ADDRESS() is absolute_relative_mode

[ OPTIONAL - 1 by default ] - An indicator of whether the reference is row/column absolute. 1 is row and column absolute (e.g. \$A\$1), 2 is row absolute and column relative (e.g. A\$1), 3 is row relative and column absolute (e.g. \$A1), and 4 is row and column relative (e.g. A1).

• Thanks ale! What's the "4" in COLUMN(),4 mean? Apr 20, 2017 at 22:16
• I've updated my answer.
– ale
Apr 21, 2017 at 12:16