Results 1 to 8 of 8

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    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.


  3. #3
    let me try that. ill reply shortly.

  4. #4
    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
    Attached Files Attached Files

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    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.


  6. #6
    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?

  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    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?


  8. #8
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    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 by JeffreyWeir; 2013-04-08 at 11:13 PM. Reason: Formula revision

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •