Color coding coloumn

[Helloitsme]

So I'm trying to make <5/10 red, 5-6.9/10 yellow, and 7-10/10 green. For the life of me I can't figure out how...

Pricebook19JAN22.xlsx
	S
1	ATS
2
3	5,8/10
4	5,5/10
5	4,8/10
6	6,3/10
7	5,3/10
8	6,1/10
9
10	2,6/10
11	10/10
12	7,2/10
Listings

Cell Formulas
Range	Formula
S2:S12	S2	=IF(COUNT(O2,Q2),ROUND(AVERAGE(O2,Q2),1)&"/10","")

Thanks in advance for any and all help!

 

[Joe4]

Use the Formula option of Conditional Formatting, and create three rules, one for each color, i.e.

Red:

=AND(COUNT(O2,Q2),ROUND(AVERAGE(O2,Q2),1)<5)

Yellow:

=AND(COUNT(O2,Q2),ROUND(AVERAGE(O2,Q2),1)>=5,ROUND(AVERAGE(O2,Q2),1)<7)

Green:

=AND(COUNT(O2,Q2),ROUND(AVERAGE(O2,Q2),1)>=7,ROUND(AVERAGE(O2,Q2),1)<=10)

 

[Helloitsme]

If only it was that easy... the outcome of this is:

Pricebook19JAN22.xlsx
	S
1	ATS
2
3	5,8/10
4	5,5/10
5	4,8/10
6	6,3/10
7	5,3/10
8	6,1/10
9
10	2,6/10
11	10/10
12	7,2/10
Listings

Cell Formulas
Range	Formula
S2:S12	S2	=IF(COUNT(O2,Q2),ROUND(AVERAGE(O2,Q2),1)&"/10","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
S:S	Expression	=AND(COUNT(O2;Q2);ROUND(AVERAGE(O2;Q2);1)>=7;ROUND(AVERAGE(O2;Q2);1)<=10)	text	NO
S:S	Expression	=AND(COUNT(O2;Q2);ROUND(AVERAGE(O2;Q2);1)>=5;ROUND(AVERAGE(O2;Q2);1)<7)	text	NO
S:S	Expression	=AND(COUNT(O2;Q2);ROUND(AVERAGE(O2;Q2);1)<5)	text	NO

Any other ideas? I should mention the blanks should also be left colorless

 

[Joe4]

Just change the first rule to this to avod the blanks:

=AND(COUNT(O2,Q2),ROUND(AVERAGE(O2,Q2),1)>0,ROUND(AVERAGE(O2,Q2),1)<5)

I tried it, and it works perfectly like this:

You made one of the most common mistakes people make when using formulas in Conditional Formatting.
If you are selecting a mult-cell range to apply it to, you MUST write the formula as it pertains to the VERY FIRST cell in your selection.
It looks like that you selected the whole column S (and not just where the data starts). So you will need to change the row reference in all the formulas to reference row 1 instead of row 2, since the first cell in your selection in S1.

If you ever see Conditional Formatting that looks like it is "shifted up/down" by a row or two, that is usually the reason.

Note that in your original example you posted, you were applying it to the range S2:S12, which is why I wrote all the formulas referencing row 2.
However, in your last posting, your change to the entire column S (S:S).

 

[Helloitsme]

I see your point thanks! The color coding still doesn't seem to work correctly though:

Pricebook19JAN22.xlsx
	S
1	ATS
2
3	5,8/10
4	5,5/10
5	4,8/10
6	6,3/10
7	5,3/10
8	6,1/10
9
10	2,6/10
11	10/10
12	7,2/10
Listings

Cell Formulas
Range	Formula
S2:S12	S2	=IF(COUNT(O2,Q2),ROUND(AVERAGE(O2,Q2),1)&"/10","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
S2:S150	Expression	=AND(COUNT(O2;Q2);ROUND(AVERAGE(O2;Q2);1)>0;ROUND(AVERAGE(O2;Q2);1)<5)	text	NO
S2:S150	Expression	=AND(COUNT(O3;Q3);ROUND(AVERAGE(O3;Q3);1)>=5;ROUND(AVERAGE(O3;Q3);1)<7)	text	NO
S2:S150	Expression	=AND(COUNT(O3;Q3);ROUND(AVERAGE(O3;Q3);1)>=7;ROUND(AVERAGE(O3;Q3);1)<=10)	text	NO

Do you reckon it's because I'm using commas instead of dots for data? This is the standard in Denmark.

 

[Joe4]

Look closely at your yellow and green formulas.
You are applying them to range S2:S150, but you are referencing row 3, not row 2 in your formulas!
Always compare your formula to the range you are applying them to, like I described in my previous post!

 

[Helloitsme]

Oh so I can't have my first conditional format cell be empty, got it, i think

There's still some issues though:

Pricebook19JAN22.xlsx
	S
1	ATS
2
3	5,8/10
4	5,5/10
5	4,8/10
6	6,3/10
7	5,3/10
8	6,1/10
9
10	2,6/10
11	10/10
12	7,2/10
Listings

Cell Formulas
Range	Formula
S2:S12	S2	=IF(COUNT(O2,Q2),ROUND(AVERAGE(O2,Q2),1)&"/10","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
S3:S150	Expression	=AND(COUNT(O3;Q3);ROUND(AVERAGE(O3;Q3);1)>0;ROUND(AVERAGE(O3;Q3);1)<5)	text	NO
S3:S150	Expression	=AND(COUNT(O4;Q4);ROUND(AVERAGE(O4;Q4);1)>=5;ROUND(AVERAGE(O4;Q4);1)<7)	text	NO
S3:S150	Expression	=AND(COUNT(O4;Q4);ROUND(AVERAGE(O4;Q4);1)>=7;ROUND(AVERAGE(O4;Q4);1)<=10)	text	NO

 

[Joe4]

Oh so I can't have my first conditional format cell be empty, got it, i think

No, that is NOT what I am saying at all. I am saying that whatever range your select to apply the Conditional Formatting too, you need to write your formula as it applies to the very first cell in your selection. Those row number references should match.

Look at what you posted:

Note how on your first CF rule (the red one), it is applied to the range S3:S150, and all your row references in that formula are for row 3?
You did that one perfectly.

But then note that last two (yellow and green). The rules are applied to the range S3:S150, but all your row references are for row 4!
That means that the formatting for cell S3 will look at row 4, the formatting for S4 will look at row 5, the formatting for S5 will look at row 6, etc
You selected cells and formula row references are off by 1 row! You need to fix this so the bottom two rules reference row 3, not row 4.

If you do not know how to fix the existing rule, then just delete those two and start again.

 

[Helloitsme]

THANK YOU for your patience and explanation, I finally got it right! Sorry to be such a bother haha...

If anyone needs this fix, here's the info:

Pricebook19JAN22.xlsx
	S
1	ATS
2
3	5,8/10
4	5,5/10
5	4,8/10
6	6,3/10
7	5,3/10
8	6,1/10
9
10	2,6/10
11	10/10
12	7,2/10
Listings

Cell Formulas
Range	Formula
S2:S12	S2	=IF(COUNT(O2,Q2),ROUND(AVERAGE(O2,Q2),1)&"/10","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
S2:S150	Expression	=AND(COUNT(O2;Q2);ROUND(AVERAGE(O2;Q2);1)>0;ROUND(AVERAGE(O2;Q2);1)<6)	text	NO
S2:S150	Expression	=AND(COUNT(O2;Q2);ROUND(AVERAGE(O2;Q2);1)>=6;ROUND(AVERAGE(O2;Q2);1)<7,5)	text	NO
S2:S150	Expression	=AND(COUNT(O2;Q2);ROUND(AVERAGE(O2;Q2);1)>=7,5;ROUND(AVERAGE(O2;Q2);1)<=10)	text	NO

Thanks so much Joe, you're a lifesaver!

 

[Joe4]

You are welcome.

Note that I went back and marked the initial post with the formulas as the solution.
All the posts after that were just working out the details to implement it correctly, but that initial post contained the needed formulas.