1. 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?

Marty

Something like this:
=IF(ISBLANK(E2),"No Data",IF(OR(E2<2.5,E2>3.5)=TRUE,1,0))

3. 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. Thank you Catharine and HansV.........

5. 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.

Regards,
Marty

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

7. 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. Originally Posted by mojave1
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))

9. Originally Posted by mojave1
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.

10. 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

11. Originally Posted by AndrewKKWalker
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. Originally Posted by mojave1
......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. Originally Posted by bosco_yip
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. Originally Posted by HansV
But the "formula blank cell" A4 is not included in the count!
Hi Hans,