Conditional Formatting Google Sheets Query

Question

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:

There's no C4, D4... in your sample. Could you show us some of your efforts in achieving your goal? — TheMaster, Oct 19 '21 at 16:03
Edited thanks, added my current approach as well. The solution is only 1 cell at a time and requires a rule for each color. Wondering if there is a more scalable solution — nak5120, Oct 19 '21 at 16:14
Post examples and rules don't make sense to me. You say that E3 should be blue, and J3 should be grey; yet both cells have a Significance of 0.8. You also say that the "blue rule" should apply "If Signficance >= 0.8 and Signficance <= 0.8"; yet that is the same as "If Significance = 0.8 exactly." — Erik Tyler, Oct 19 '21 at 16:57
Some explanations on [this thread](https://stackoverflow.com/questions/20545961/conditional-formatting-based-on-another-cells-value/69635326#69635326) — TheMaster, Oct 19 '21 at 17:33

MattKing · Answer 1 · 2021-10-19T17:48:10.450

2

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.

edited Oct 19 '21 at 17:48

answered Oct 19 '21 at 17:28

MattKing

7,373
8
13

score 1 · Accepted Answer · answered Oct 19 '21 at 17:25

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.

Conditional Formatting Google Sheets Query

2 Answers2