PDA

View Full Version : Solved: Not important, error with the ISBLANK function



Daxton A.
01-02-2013, 06:17 PM
If I type =ISBLANK(G7:M14)
the value comes up false, (G7:m14 are in fact blank by the way.)

But if I type in the function pertaining to 1 cell it comes back true...and this is for all the cells G7 to M14. I don't get why? Can sm1 please explain this to me...

Much appreciated,
Daxton

Teeroy
01-02-2013, 07:09 PM
As I understand it ISBLANK is designed for a single cell and will only evaluate the first cell of the range supplied. If you are checking for blanks try something like =IF(COUNTBLANK(G7:M14), TRUE, FALSE).

Aflatoon
01-03-2013, 02:01 AM
It actually depends on the input range, where the formula is located and how it is entered.
For a normally entered formula (i.e. non-array), if the input range is a single row or column and the formula cell is directly to the side of/below exactly one of the referenced cells, then that cell will be evaluated by the ISBLANK. In all other situations (multiple rows/columns and/or no intersection with the formula cell and the range's rows/columns) the range evaluates to #VALUE and ISBLANK returns FALSE.
For an array-entered formula entered into one cell, ISBLANK evaluates the first cell only; for an array formula in multiple cells, ISBLANK evaluates each cell in turn for as many cells as the formula occupies.

If you want to test if all cells are blank or not, you can also use:
=AND(ISBLANK(G7:M14))
array-entered into one cell.

FWIW.

Teeroy
01-03-2013, 02:35 AM
Aflatoon, thanks for the detailed explanation. I also found that a normally entered formula acts as you've described an array entry in a single cell (in excel 2003). I ignored array entry of ISBLANK as it didn't seem to provide any value over multiple single cell entries but using the AND to reduce the array to a single resultant is a great enhancement.:thumb

Aflatoon
01-03-2013, 03:23 AM
I also found that a normally entered formula acts as you've described an array entry in a single cell (in excel 2003).

It depends where the formula cell is, as I mentioned.