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

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?

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.

let me try that. ill reply shortly.

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

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.

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?

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?

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)))