Conditional Formatting Google Sheets Query-CodePudding

I am trying to write a query in google sheets that will affect conditional formatting. Below is my example dataset

        A                   B    C    D   E   F   G     H    I    J
1    Signficance            1  0.5 -0.9 0.8 0.7 0.6  0.95 0.94  0.8 
2    Number of Events      13   13   13  13  13  13    10    8   13 
3    Lift                   9    4   12   9   5   4    10   12   10

I want to fill in the following colors in the lift row with these rules:

If Signficance >= 0.9, then highlight corresponding Lift cell with Green
If Signficance >= 0.8 and Signficance < 0.9, then highlight corresponding Lift cell with Blue
If Signficance <= -0.9, then highlight corresponding Lift cell with Red 
Any other significance, highlight corresponding Lift cell with Gray

The desired result will highlight the following cells as:

B3 = Green
C3 = Gray
D3 = Red
E3 = Blue
F3 = Gray
G3 = Gray
H3 = Green
I3 = Green
J3 = Blue

Here is my attempt which is time consuming for every cell:

CodePudding user response：

4 rules:

Apply to Range for each: B3:3

Rule 1, Custom:
=B1<=-0.9
Red

Rule 2,Custom:
=B1>=0.9
Green

Rule 3,Custom:
=B1>=0.8
Blue

Rule 4, "Cell is not Empty"
Grey

It's important that the rules are in that order. As the order in which the rules are listed creates priority among the rules and allows you to keep them simpler.

CodePudding user response：

Select the entire Row 3 by clicking on the 3 to the outside left of the grid.

Then you will apply four custom CF rules (i.e., each rule will have "Custom formula is..." selected under Format rules / Format cells if...). There are other ways to set up these rules, but I'm choosing this method for ease of explanation and process.

Apply the following CF rules in order:

1st / green rule Enter the following formula in the field below "Custom formula is...": =AND(ISNUMBER(A3),A1>=0.9) Select a green background color and click "Done."

2nd / blue rule Enter the following formula in the field below "Custom formula is...": =AND(ISNUMBER(A3),A1>=0.8) Select a blue background color and click "Done."

3rd / red rule Enter the following formula in the field below "Custom formula is...": =AND(ISNUMBER(A1),A1<=-0.9) Select a red background color and click "Done."

4th / gray rule Enter the following formula in the field below "Custom formula is...": =ISNUMBER(A3) Select a gray background color and click "Done."

It's important that the rules be in the above order, with green at the top and gray at the bottom of the CF rules stack.