Formula for first blank cell in a range

[ccordner]

I am producing a roster and need to find out if a person has a day off during a week.

At the moment, I have a very complicated formula saying "if(g3="",...,if(h3="",.. etc.

Is there any formula I can use to find the first blank cell in a range (1 row x 7 columns)? Specifically, I need it to return the column number (either within the range, or absolute).

Thanks
Chris

 

[Krishnakumar]

Hi,

May be..

=IF(COUNTBLANK(G3:M3),MATCH(TRUE,INDEX(G3:M3="",0,0),0),"")

adjust the range

 

[T. Valko]

I am producing a roster and need to find out if a person has a day off during a week.

At the moment, I have a very complicated formula saying "if(g3="",...,if(h3="",.. etc.

Is there any formula I can use to find the first blank cell in a range (1 row x 7 columns)? Specifically, I need it to return the column number (either within the range, or absolute).

Thanks
Chris

This array formula** will return the RELATIVE column number.

=IF(COUNTBLANK(A1:G1),MATCH(TRUE,A1:G1="",0),"")

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[ccordner]

Thanks very much.

What's the difference between an array formula and a normal formula?

Chris

 

[T. Valko]

Thanks very much.

What's the difference between an array formula and a normal formula?

Chris

See this:

http://www.cpearson.com/Excel/ArrayFormulas.aspx

 

[Willmonbo]

T. Valko

I just love the formula above, it's a construction that I haven't seen before, I get the idea but do not understand the logic of how it evaluates, if it's not too tedious to explain it i'd greatly appreciate it, or if you can point me to a writing that explains it that would even be better, you can do it here or feel free to PM me if you think it's more appropriate.

thanks much for adding so much to the board.

 

[T. Valko]

T. Valko

I just love the formula above, it's a construction that I haven't seen before, I get the idea but do not understand the logic of how it evaluates, if it's not too tedious to explain it i'd greatly appreciate it, or if you can point me to a writing that explains it that would even be better, you can do it here or feel free to PM me if you think it's more appropriate.

thanks much for adding so much to the board.

Let's use this sample data:

Book1
	A	B	C	D	E	F	G
1	75	29		30	55	32	61
Sheet1

Array entered**:

=IF(COUNTBLANK(A1:G1),MATCH(TRUE,A1:G1="",0),"")

Since we want to find the position of the first blank/empty cell in the range the very first thing we need to do is to make sure there is in fact a blank/empty cell within the range.

COUNTBLANK(A1:G1) will return the count of cells that are blank/empty.

=IF(COUNTBLANK(A1:G1) returns any number >0 then find the first blank/empty cell in the range by executing this test: MATCH(TRUE,A1:G1="",0). If =IF(COUNTBLANK(A1:G1) returns 0 then return blank "" as the result of the formula.

In this example COUNTBLANK(A1:G1) returns 1 so the formula then processes this test: MATCH(TRUE,A1:G1="",0).

We want to find the first position where the logical value TRUE will appear in an array. The MATCH function will return the relative position of the lookup value within an array. In ths case the lookup value is the logical value TRUE and the array is generated by this expression:

A1:G1=""

That expression will generate an array of TRUE or FALSE like this:

A1:G1=""

A1="" = FALSE
B1="" = FALSE
C1="" = TRUE
D1="" = FALSE
E1="" = FALSE
F1="" = FALSE
G1="" = FALSE

Within that array the first (and only) TRUE is located at position 3.

So:

MATCH(TRUE,{FALSE,FALSE,TRUE,FALSE,FALSE,FALSE,FALSE},0) =
3

And:

The position of the first blank/empty cell within the range A1:G1 is:

=IF(COUNTBLANK(A1:G1),MATCH(TRUE,A1:G1="",0),"")

=3

 

[Willmonbo]

got it, so the array in the match function is entered here as an array with a logical test to evaluate every cell in the array if I understand you correctly.

 

[T. Valko]

got it, so the array in the match function is entered here as an array with a logical test to evaluate every cell in the array if I understand you correctly.

Yep, that's it.

 

[Kdilas]

Would it be possible to use something like this in VBA? I currently have a macro that searches a range for a value supplied by the user and if found Highlights that cell. I also need to place the user's search value into the last blank cell in a range but currently cannot figure out how to do that. Any help would be greatly appreciated.