How to apply a relative reference in conditional formatting formula?

Question

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.

Sheet link

Would a script be better for this kind of feat?

Answer 1

Try this:

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

INDIRECT
ADDRESS ¹ ROW
COLUMN

¹ 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).