ISBLANK(True, False) and Array

Question

I'm trying to figure out if a series of 5 cells are blank or not using the ISBLANK arrayed function. The function seems to be returning the wrong result however. Anytime the first cell returns a "TRUE" value, the result will still read "FALSE".

For clarity's sake, I'll illustrate it out below. My data more or less looks like this:

	A	B	C	D
1	{=ISBLANK(B1:D1)}	50%
2	{=ISBLANK(B2:D2)}		50%
3	{=ISBLANK(B3:D3)}			50%

When I evaluate the formula, the results will give me the following:

	A	B	C	D
1	{FALSE, TRUE, TRUE}	50%
2	{TRUE, FALSE, TRUE}		50%
3	{TRUE, TRUE, FALSE}			50%

However, the final result seems to be inconsistent, as I receive the following:

	A	B	C	D
1	FALSE	50%
2	TRUE		50%
3	TRUE			50%

Is there something particular about the first value that is forcing the function to return a "FALSE" result, when all the other values are returning a "TRUE" result?

Answer 1

> ... I'm trying to figure out if a series of 5 cells are blank or not using the ISBLANK arrayed function.

You have the answer using COUNTBLANK.

However, for you question, you need to transform the True/False to values.

The most common way would be:

=SUM(--ISBLANK(A1:E1))=5

I like to use:

=SUM(0+ISBLANK(A1:E1))=5

Answer 2

Hi Sillinesses,

Just as an alternative, try:

=COUNTA(B1:D1)=0

===

Regards,

Norman

Answer 3

ISBLANK() returns the correct result. It returns the first result in first cell.

You can use this array formula to return all FALSE:

{=SUM(IF(B1:D1<>"",0,1))=0}

Answer 4

Unfortunately for some reason, it's counting the percentages as blanks as well. It will return the following for me:

	A	B	C	D
1	{=COUNTBLANK(B1:D1)}	50%
2	{=COUNTBLANK(B2:D2)}		50%
3	{=COUNTBLANK(B3:D3)}			50%

Which will return,

	A	B	C	D
1	{=COUNTBLANK("50%", 0, 0)}	50%
2	{=COUNTBLANK(0,"50%",0)}		50%
3	{=COUNTBLANK(0,0,"50%")}			50%

Which will result in

	A	B	C	D
1	3	50%
2	3		50%
3	3			50%

Which doesn't seem right to me... For whatever reason it doesn't register the "50%" as not a blank.

Answer 5

Hi,

Enter this formula in cell A1 and copy down

=COUNTBLANK(B1:D1)

Hope this helps.