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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!!!
 
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
 
Back
Top