How to find the first non-blank number in a row?

[Guest]

I have several rows which all start out as blank cells but then over time
start to have numbers (though the numbers start to come at different times).
I would like to write a formula that pulls the first non-blank cell for each
row. The numbers (once they start) are not sequential so i cannot use a
min/max function. There are also sporadic blanks which poses another hurdle.

For example:

A B C D E
Row
1. 15 11 23
2. 21 12 17
3. 2 4 78 65 13
4. 18 12

The formula i would like to write would result in the following:
1. 15
2. 21
3. 2
4. 18

I would assume there is a function that says "Return me the number in the
first cell in this row that is greater than 0"...I just cannot figure it out.
Please advise.

THANKS!!!

 

[Guest]

=INDEX(1:1,MATCH(TRUE,1:1<>"",0))

ctrl+shift+enter, not just enter
copy down

 

[T. Valko]

Try this array formula** :

=INDEX(A1:E1,MATCH(TRUE,ISNUMBER(A1:E1),0))

Copy down as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

 

[Peo Sjoblom]

That would return text as well


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

 

[Guest]

Is there an option that does not require control+shift+enter? Thanks for the
help.

 

[Guest]

Is there an option that does not require control+shift+enter? Thanks for the
help.

 

[T. Valko]

Try this normally entered:

=INDEX(A1:E1,MATCH(1,INDEX(--(ISNUMBER(A1:E1)),1,),0))

Biff

 

[Guest]

=INDEX(1:1,MATCH(1,INDEX(--(1:1<>""),1,0),0))

Is there an option that does not require control+shift+enter? Thanks for the
help.