Conditional formatting question

[BigBonna]

Hello,

I have a table similar to the one below. The bold numbers are the maximum scores for a column.

Person	5	4	6	4	3	6
A	4	6	2	5	2	2
B	1	5	5	4	1	1
C	3	1	6	2	6	3
D	6	3	6	5	2	1

<tbody>
</tbody>

I would like to create a conditional formatting rule that would colour the cells in column B:

• red if the value is < b1/3
• yellow if the value is >= b1/3 but <= 2*b1/3
• green if the value is < 2*b1/3
• blue if the value is > b1 (to signify an error)

I used B as an example, but I would like to do this for all columns.

Is it possible to create this in one rule? If so could someone please link me to an appropriate learning resource? I am having no luck searching.

Thank you.

<colgroup><col style="width:48pt" span="7" width="64"> </colgroup><tbody>
</tbody>

 

[Andrew Poulsom]

Select B2:G5 and apply the following Conditional Formatting formulas:

=B2<(B$1/3)
=AND(B2>=(B$1/3),B2<=(B$1*2/3))
=B2>(B$1*2/3)
=B2>B$1

 

[BigBonna]

That's wonderful. Thank you very much

Would you care to explain briefly how that works? Conditional formatting is something I'm yet to explore to its full potential and I'm eager to learn.

Edit: It turns out my "Person" cell (A1) is actually E4 on my spreadsheet. Naturally the formulae won't work, even when I try changing them to the new ranges/cells and replacing B with F. Any advice?

 

[Andrew Poulsom]

It just evaluates the formulas as if they were in a cell and applies the format of the condition that returns TRUE. Here's a tutorial:

http://www.contextures.com/xlCondFormat01.html

 

[BigBonna]

It just evaluates the formulas as if they were in a cell and applies the format of the condition that returns TRUE. Here's a tutorial:

http://www.contextures.com/xlCondFormat01.html

Ignore my edit from before, I was looking at B$1/3 as B * 1/3 for some bizarre reason - too much work, too little sleep!

One more question, how would I go about setting it so there is no format if the cell is blank?

 

[BigBonna]

Never mind, problem solved. I was doing the right thing (=b2=""), it was just below the other formats for some reason. Thanks for all of the help, Andrew.