Blank Excel Cells

Topic

New Reply

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

Reply | Quote

Replies

Try adding the ISBLANK function to your formula.
Something like this:
=IF(ISBLANK(E2),”No Data”,IF(OR(E23.5)=TRUE,1,0))

Reply | Quote

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(E23.5))

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

Reply | Quote

Thank you Catharine and HansV………

Reply | Quote

Thank you Catharine and HansV………

You can also use =”” in the place of ISBLANK

=IF(E2=””,””,IF(OR(E23.5)=TRUE,1,0))

Reply | Quote

You can also use =”” in the place of ISBLANK
=IF(E2=””,””,IF(OR(E23.5)=TRUE,1,0))

Or, using only one IF function is enough

=IF(E2=””,””,(E23.5))

Regards
Bosco

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote