Excel Conditional Formatting Question

Factual Questions
1

I have a spreadsheet that has three columns where a client can type in a Yes or No answer. Based on those answers, I need a cell in a different column to change color. If the answer to all three questions is Yes, the cell is green. If the answer to only two questions is Yes, the cell is yellow. If only one or no cells is Yes, the cell is red.

In the past, the client has chosen either 1 for yes or 0 for no, which made formatting with a formula much easier. But we’d like to have the client simply type in Yes or No instead of 1 or 0.

I’ve tried to figure this out under conditional formatting, but have had no luck.

Any suggestions?

2

I was able to set this up as
Formula Is: =COUNTIF(B2:D2,“Yes”)=1
Red

etc. This relies on the lookup cells being adjacent. If they’re not, the formula would be a bit more complex, but it should be doable the same way.

3

That worked perfectly!!! Thanks so much!

4

if they’re not adjacent - or if you just want to make it needlessly complex, you could:

Make the cell Red by default

Plug in =IF(C5=“yes”,1,0)+IF(D5=“yes”,1,0)+IF(E5=“yes”,1,0)=2
and choose format fill yellow under use a formula

Plug in =IF(C5=“yes”,1,0)+IF(D5=“yes”,1,0)+IF(E5=“yes”,1,0)=3
and have that format green

changing C5, D5, and E5 to whatever cells you wanted

or, if you didn’t mind having a number in the cell with the formatting, you could use the countif function or:
=IF(IF(C5=“yes”,1,0)+IF(D5=“yes”,1,0)+IF(E5=“yes”,1,0)>2,3,IF(IF(C5=“yes”,1,0)+IF(D5=“yes”,1,0)+IF(E5=“yes”,1,0)<2,0,1))
to place a number in the cell

and then use the default 3 color color scales conditional formatting, setting the min mid and max to the “number” settign and choosing values of 0,1,2 for red, yellow, green

or you could use that formula in a dummy cell and then reference it with a conditional formatting, so many options

SCS’s solution is much cleaner, but you can score more points with a longer formula

5

That doesn’t spit out green or yellow though, does it?

6

I think you would just use SCS’s formula multiple times, with defining different results as different fills. I would probably just default the cell to red and then use =2 to make it yellow and =3 to make it green

7

I used the formula with conditional formatting and changed the cell color based on the number output (green = 3, yellow = 2, red < 2) and it worked like a charm! :slight_smile:

8

Did you put the formula in the cell, or put the formula in the conditional formatting?

i.e. do you have the number of yes’s shown in the cell, or does the cell say something else?

9

That’s what the ‘etc.’ was for. I presumed that anyone already messing with conditional formatting in Excel could figure out how to write the other conditions based on that sample. :slight_smile:

10

Ah, of course.

11

I put the formula in conditional formatting. I have three rules set up, one for each color and the color changes based on the number of yes’s.

12

Hey, thanks. I’d been wondering how to do this.

An additional question:

If the “If” value is false, is there a way to make it so that it leaves the cell blank instead of showing “FALSE” or a zero?

In other words, how do you make a formula return a blank?
(Other than doing conditional formatting so that if the cell value is “0” then the font turns white. :stuck_out_tongue: )

13

Much different question than the OP but the idea is

=IF(A1=5,“It’s Five”,"")

**ETA: **Note that is a blank cell in the sense of “the users sees a blank cell.” It will not be considered blank for purposes of COUNTA, for example.

14

But if you want to count the number of cells in a range that aren’t blank where you have cells that are “blank” from having a conditional statement where the result is “”, then just use counta(range)-countif(range,"")

However, if you have legitimately blank cells in there (just plain old empty), you will need to change those cells to ="" otherwise your count will come out too low as they will be ‘double-counted’ as blank

15

Yup. To better understand it, try reading the equation as the following (the parentheticals are what the text is referring to):

“If the cell I’m pointing to (A1) equals 5 (A1=5), then (the comma) say ‘It’s Five’, if not (the next comma), then say nothing (the “” part).”

It might be more clear if the equation was:

=IF(A1=5,“It’s Five”,“It’s Not Five”)

You can leave off the final clause (“It’s Not Five”) and if A1 isn’t 5 then it’ll spit out “FALSE”.

16

Actually, this muddies things since Green Bean specifically asked how to produce a blank* instead of* FALSE.

17

This is a common question and the answer is that Excel formulas can’t return an empty cell. You can do it with VBA but that’s a different beast.

18

But the question wasn’t how to make the cell empty, it was how to return a blank. Depends on why you need it blank, but blank and non-empty is perfectly appropriate for many purposes.

19

I interpreted the question like this…

suppose in cell F5 you have a formula like this:


=if(B5 <> A5, "different!", "")

…for most purposes, the zero-length string “” is good enough.

However, there is no function or special magic constant to replace the double quotes “” to make the cell F5 act as if there was nothing ever put there – empty or NULL (or “blank” in casual parlance) in other words. That’s how I interpreted “blank” in his question. I guess I read the question that way because anybody who ever asks about blank usually already knows about the the zero-length string. The poster inevitably responds with, *“I already know about the double quote trick but that doesn’t exactly do what I want blah blah *blah…”

20

Well, if it makes you feel better, I didn’t know about the double-quote thing. :slight_smile:

Thanks for your help, everyone.