Trying to get a cell to remain blank

Tazyote

New Member
Joined
Jan 14, 2014
Messages
14
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
:confused: ;)
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Maybe:
Code:
=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(j2:p2),"",if(isna(match("no",j2:p2,0)),"pass","fail"))
 

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,680
Office Version
  1. 2010
Platform
  1. Windows
I think this normally entered formula will also work...

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

Watch MrExcel Video

Forum statistics

Threads
1,122,489
Messages
5,596,455
Members
414,069
Latest member
StudExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top