Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Brighton, Michigan
    Posts
    184
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Hi To All,

    I've attached a section of an Excel spreadsheet, the data is for various electrical tests. Specifically, column E addresses resistance measurement data, with the help of this site I have a logic statement checking for resistance measurements that are greater or less than a range of 2.5 to 3.5 resistance in ohms. The test returns a 1 is the number exceeds this range and a 0 is it meets the range. However, when a cell is empty it treats it as a 1. Any suggestions as to how to correct this?

    thank you for your assistance

    Marty
    Attached Files Attached Files
    Regards,
    Marty

    "Aerodynamics Is For Those Who Cannot Build Engines" - Enzo Ferrari

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Try adding the ISBLANK function to your formula.
    Something like this:
    =IF(ISBLANK(E2),"No Data",IF(OR(E2<2.5,E2>3.5)=TRUE,1,0))
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Blank cells count as 0 so they fall outside the range. As WebGenii suggests, you need to add an extra check for blank cells.
    The formula can be simplified slightly:

    =IF(ISBLANK(E2),"",1*OR(E2<2.5,E2>3.5))

    This version returns an empty string if E2 is blank; you can make it return something else by changing "" in the formula.

  4. #4
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Brighton, Michigan
    Posts
    184
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Thank you Catharine and HansV.........
    Regards,
    Marty

    "Aerodynamics Is For Those Who Cannot Build Engines" - Enzo Ferrari

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Brighton, Michigan
    Posts
    184
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Hi Catharine and HansV,

    This is an extension to the original post. While using the "COUNTA and COUNTBLANK" functions to count the number of cells within the same column that contain, a 1, a 0 or are blank, the COUNTBLANK function returns a correct value, those cells that are blank. Yet the COUNTA function merely counts the total number of cells within the specified range, whether a 1, 0 or blank.

    Any suggestions? I've followed the sparse excel help window w/ no success.

    Many Thanks for your help...

    Regards,
    Marty
    Regards,
    Marty

    "Aerodynamics Is For Those Who Cannot Build Engines" - Enzo Ferrari

  6. #6
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    I'm not clear on what you want to do? COUNTA counts cells with alphanumeric contents, which means it will count a cell containing, a formula, numbers or text. COUNT counts the cells with numeric contents which means it will count a cell containing a number or a cell containing a formula that returns a number.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    COUNTA does not include cells that are really blank, i.e. cells that contain neither a constant value nor a formula, but it does include cells that contain a formula that results in an empty string - such cells may appear blank, but Excel doesn't consider them to be blank.

  8. #8
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by mojave1 View Post
    Thank you Catharine and HansV.........
    You can also use ="" in the place of ISBLANK

    =IF(E2="","",IF(OR(E2<2.5,E2>3.5)=TRUE,1,0))
    Andrew

  9. #9
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by mojave1 View Post
    This is an extension to the original post. While using the "COUNTA and COUNTBLANK" functions to count the number of cells within the same column that contain, a 1, a 0 or are blank, the COUNTBLANK function returns a correct value, those cells that are blank. Yet the COUNTA function merely counts the total number of cells within the specified range, whether a 1, 0 or blank.
    You could try using =COUNTIF(F2:F30,1)+COUNTIF(F2:F30,0) to count the 1 and 0 values.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  10. #10
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Brighton, Michigan
    Posts
    184
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Catharine, HansV, & Andrew,

    Thank you for your input. I was using the wrong statement. The distinction between COUNTBLANK and COUNTA is now clear. Thank you again for your assistance.

    Regards,
    Marty
    Regards,
    Marty

    "Aerodynamics Is For Those Who Cannot Build Engines" - Enzo Ferrari

  11. #11
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by AndrewKKWalker View Post
    You can also use ="" in the place of ISBLANK
    =IF(E2="","",IF(OR(E2<2.5,E2>3.5)=TRUE,1,0))
    Or, using only one IF function is enough

    =IF(E2="","",(E2<2.5)+(E2>3.5))

    Regards
    Bosco

  12. #12
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by mojave1 View Post
    ......While using the "COUNTA and COUNTBLANK" functions to count the number of cells within the same column that contain, a 1, a 0 or are blank, the COUNTBLANK function returns a correct value, those cells that are blank. Yet the COUNTA function merely counts the total number of cells within the specified range, whether a 1, 0 or blank.
    Marty
    Herein the examples in explanation of using COUNT, COUNTA, COUNTBLANK and COUNTIF

    Testing datas at Column A1:A5

    ......Col A...
    ..1....M......
    ..2....8......
    ..3.... ...... ( A3 is a blank cell )
    ..4.... ...... ( A4, is a formula blank cell, with enter : ="" )
    ..5....9......

    1] COUNTBLANK…..Count no of blank cells (blank and formula blank cells)

    =COUNTBLANK(A1:A5)

    =2 (A3+A4)

    2] COUNTA…..Count no of non-blank cells, include formula blank cell

    =COUNTA(A1:A5)

    =4 (A1+A2+A4+A5)

    3] COUNT…..Count no of numeric cells, exclude formula blank cell

    =COUNT(A1:A5)

    =2 (A2+A5)

    4] =COUNTIF…..Count no of text cells, include formula blank cell

    =COUNTIF(A1:A5,"*")

    =2 (A1+A4)

    5] =COUNTIF…..Count no of text cells, exclude formula blank cell

    =COUNTIF(A1:A5,"?*")

    =1 (A1)

    Remark : COUNTIF is a conditional count function, please refer to the Help File for further information

    Hope can help

    Regards
    Bosco

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by bosco_yip View Post
    3] COUNT…..Count no of numeric cells, include formula blank cell

    =COUNT(A1:A5)

    =2 (A2+A5)
    (Italics are mine)

    But the "formula blank cell" A4 is not included in the count!

  14. #14
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by HansV View Post
    But the "formula blank cell" A4 is not included in the count!
    Hi Hans,

    Oh!... my mistake!.....the mistake was corrected as per your advised.

    Thank you for your good catch

    Regards
    Bosco

Posting Permissions

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