Conditional Formatting Question Request

[adamworton]

Hi all.

I want to conditionally format a cell. The cell at the moment has in it the calculation of a weighted average in it from four cells above it.

• I want the cell to be formatted red if the four cells from which it calculates the weighted average from are blank.
• Yellow if at least one of the cells from which it calculates the weighted average from has a value between 0-100 in it.
• Green for when all four cells it calculates the weighted average from have a value between 0-100

I've been trying to work it out myself with the conditional formatting options but I can't seem to get it to work. Is it even possible?

Thanks in advance.

Adam

 

[RoryA]

Welcome to the forum.

You basically need two separate conditional formats. I'll assume your cell is A5 for this example.

First, set a default format of green for the cell manually.
Second, set a CF using the formula:
=COUNTIF($A$1:$A$4,">=0")<4
using a yellow fill.
Third, set a CF using the formula:
=COUNTBLANK($A$1:$A$4)=4
using a red fill and Stop If True enabled.

I've assumed the four cells will only be blank (ie actually empty) or contain a number between 0-100. If there could be other entries, we'll need to adjust the setup.

 

[adamworton]

Thank you RoryA.

This has worked perfectly.

Kind regards,

Adam

 

[adamworton]

Hi RoryA,

I am now trying to adjust the formula you have provided to do something which seems like it should be able to do but again, I can't seem to get it to work.

I want the same colour formatting but it's now looking at not just four cells, but 13 cells which are in difference columns. If i highlight the cells that I want it to count in the formula then it is telling me that the formula has too many arguments. Is there anyway I can adjust it to allow counting from non adjacent cells?

Regards,

Adam

 

[RoryA]

COUNTIF doesn't really play well with discontiguous cells. Which cells do you need to include?

 

[adamworton]

Its four columns of cells. For example:

I want R5 to be green/yellow or red based on how many cells out of A1-A4, D2-D4, G3-G4 and J4 are blank.
All blank = Red
At least one with a value between 0 and 100 = Yellow
All with a value between 0 and 100 = Green.

Cheers,

Adam

 

[RoryA]

Try using COUNT($A$1:$A$4,$D$2:$D$4,$G$3:$G$4,$J$4)<10 as the second formula.

 

[adamworton]

Thanks RoryA,

That has worked for the yellow formatting, and it defaults to green when none of the cells are blank. But i should have mentioned earlier that the COUNTBLANK doesn't work either for discontinuous cells....


Any advice?

Many thanks for all your help so far!

Cheers,

Adam

 

[RoryA]

That's OK - we can use COUNT there too:
=COUNT($A$1:$A$4,$D$2:$D$4,$G$3:$G$4,$J$4)=0