if a cell in a range is blank, it should return an error.Do i use a formula for that?

chaos247

New member
Joined
Apr 2, 2013
Messages
5
Reaction score
0
Points
0
Hi,

I have a range of cells that have a drop down list in them. These affect an ovearall percentage. I need to ensure that every cell in that range has a Yes/No/N\A. If even just one cell does not have a value, then it must return an error.

Is there a formula for that?
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
You can use possibly COUNTBLANK

e.g.

=IF(COUNTBLANK(X1:X10)>0,"Error",your-formula)

where X1:X10 is your range of cells, and all either have a value or not.
 

chaos247

New member
Joined
Apr 2, 2013
Messages
5
Reaction score
0
Points
0
The formula im using is =IF(OR(E20="",E21="",E22),"",SUMIF(E20:E22,"Yes",$C20:$C22)/(1-SUMIF(E20:E22,"N/A",$C20:$C22)/SUM($C20:$C22))*IF(COUNTIFS($D20:$D22,"auto zero",E20:E22,"No"),0,1))

When applying your part i end up with:

=IF(COUNTBLANK(X1:X10)>0,"Error",IF(OR(E20="",E21="",E22),"",SUMIF(E20:E22,"Yes",$C20:$C22)/(1-SUMIF(E20:E22,"N/A",$C20:$C22)/SUM($C20:$C22))*IF(COUNTIFS($D20:$D22,"auto zero",E20:E22,"No"),0,1))

The part highlighted in red is designed to keep the cell blank, however when i apply your part of the formula, the cell value is error until all relevant cells are populated. Then the percentage is displayed. See E23. File attached
 

Attachments

  • Test sheet 3_etaf 2.xlsm
    45.8 KB · Views: 8

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
you can pull it to the front:

=IF(OR(E20="",E21="",E22),"",IF(COUNTBLANK(X1:X10)>0,"Error",SUMIF(E20:E22,"Yes",$C20:$C22)/(1-SUMIF(E20:E22,"N/A",$C20:$C22)/SUM($C20:$C22))*IF(COUNTIFS($D20:$D22,"auto zero",E20:E22,"No"),0,1)))

you may forgotten to include the ="" after E22 in that red part.... check again.
 

chaos247

New member
Joined
Apr 2, 2013
Messages
5
Reaction score
0
Points
0
IF i copy your formula in E23

=IF(OR(E20="",E21="",E22=""),"",IF(COUNTBLANK(X1:X10)>0,"Error",SUMIF(E20:E22,"Yes",$C20:$C22)/(1-SUMIF(E20:E22,"N/A",$C20:$C22)/SUM($C20:$C22))*IF(COUNTIFS($D20:$D22,"auto zero",E20:E22,"No"),0,1)))

Even if all the drop downs are Yes or No, the value in E23 is still error? Am i doing something wrong?
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
I am not sure I understand then.

This part: IF(OR(E20="",E21="",E22=""),""... says to that if any of the 3 cells are blank, return a blank... and this part: IF(COUNTBLANK(E20:E21)>0,"ERROR".. (i used X1:X10) in my example, for you to adjust to your data) would say if any of the cells are blank return "ERROR".

I am not sure which you want?
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
Looking at your spreadsheet, I think you want something like this:
=IF(COUNTBLANK(E20:E22)=3,"",IF(COUNTBLANK(E20:E22)<3,"Not Finished",SUMIF(E20:E22,"Yes",$C20:$C22)/(1-SUMIF(E20:E22,"N/A",$C20:$C22)/SUM($C20:$C22))*IF(COUNTIFS($D20:$D22,"auto zero",E20:E22,"No"),0,1)))

Note I made it say "Not Finished" rather than "Error".

Edit: Whoops, I meant to put this:
=IF(COUNTBLANK(E20:E22)=3,"",IF(COUNTBLANK(E20:E22)>0,"Not Finished",SUMIF(E20:E22,"Yes",$C20:$C22)/(1-SUMIF(E20:E22,"N/A",$C20:$C22)/SUM($C20:$C22))*IF(COUNTIFS($D20:$D22,"auto zero",E20:E22,"No"),0,1)))
 
Last edited:
Top