Trying to get a cell to remain blank

[Tazyote]

I am not sure that there is a solution to this one but I thought I could try. I have a Range of cells with Drop Down Lists. The Cells are in the range of J2:P2. The Drop down has 3 options, Yes, No, and NA (or Blank). What I am trying to do is make Cell R2 Read either "Pass" if all of the cells are populated NA or Yes or "Fail" if any cell in the range equals "No". If any or all of the cells are left blank, I need R2 to be blank as well.

I have used so far:

=IF(OR(J2="No",K2="No",L2="No",M2="No",N2="No",O2="No",P2="No"),"Fail","Pass")

This makes the cell R2 default to "Pass" even when there are cells in the range left unpopulated. I need R2 to remain blank until all of the cells in the range are populated. The reason for this is that I am totaling the answers at the bottom to determine the percentages of pass and fails. If everything defaults to "Pass", it skews the number.

I want it to do this:

J2	K2	L2	M2	N2	O2	P2	R2
Yes	Yes	Yes	Yes	Yes	Yes	Yes	Pass

<TBODY>
</TBODY>

or:

J2	K2	L2	M2	N2	O2	P2	R2
Yes	NA	Yes	Yes	NA	Yes	Yes	Pass

<TBODY>
</TBODY>

Because all of the cells contain a "Yes" or an "NA", the final Cell gives a "Pass"
---------------------------------------------------------------------------------------------
or:

J2	K2	L2	M2	N2	O2	P2	R2
Yes	Yes	Yes	Yes	No	Yes	Yes	Fail

<TBODY>
</TBODY>

Because one (or more) of the Cells contains a "No", the final Cell gives a "Fail"
---------------------------------------------------------------------------------------------
or:

J2	K2	L2	M2	N2	O2	P2	R2
Yes		Yes	Yes	Yes	Yes	Yes

<TBODY>
</TBODY>

Because one (or more) of the Cells has been left blank, the final Cell also remains blank.
---------------------------------------------------------------------------------------------
The formula for R2: =IF(OR(J2="No",K2="No",L2="No",M2="No",N2="No",O2="No",P2="No"),"Fail","Pass")
does allow the first two sections to work but will leave R2 defaulted as "Pass" when cells are left blank. This adultrates the formula averaging the passes and fails.

I am unsure if there is a solution but any ideas would be appreciated. Thanks

 

[Brian from Maui]

Maybe something like.........

=IF(COUNTA(J2:P2) < 7,"",IF(SUM(COUNTIF(J2:P2,{"yes","n/a"})=7),"pass","fail"))

There should be a more elegant way.

 

[JoeMo]

Maybe:

=IF(COUNTA(J2:P2) < COLUMNS(J2:P2),"",IF(ISNA(MATCH("no",J2:P2,0)),"Pass","Fail"))<columns(j2:p2),"",if(isna(match("no",j2:p2,0)),"pass","fail"))

</columns(j22),"",if(isna(match("no",j22,0)),"pass","fail"))

 

[Weazel]

I'm thinking there's a better way but maybe something like....


=IF(OR(J2:P2=""),"",IF(SUM((J2:P2="Yes")+(J2:P2="NA"))<7,"Fail","Pass"))

Control + Shift + Enter

 

[Tazyote]

Works great! Thanks

 

[Rick Rothstein]

I think this normally entered formula will also work...

=IF(COUNTBLANK(J2:P2),"",IF(COUNTIF(J2:P2,"No"),"Fail","Pass"))