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

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

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

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

ctrl+shift+enter, not just enter
copy down


"RLind" wrote:

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

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

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

"RLind" wrote in message
...
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!!!

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

That would return text as well


--


Regards,

Peo Sjoblom

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


"Teethless mama" wrote in message
...
=INDEX(1:1,MATCH(TRUE,1:1<"",0))

ctrl+shift+enter, not just enter
copy down


"RLind" wrote:

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

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

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

"Teethless mama" wrote:

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

ctrl+shift+enter, not just enter
copy down


"RLind" wrote:

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

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

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

"T. Valko" wrote:

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

"RLind" wrote in message
...
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!!!

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

Try this normally entered:

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

Biff

"RLind" wrote in message
...
Is there an option that does not require control+shift+enter? Thanks for
the
help.

"T. Valko" wrote:

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

"RLind" wrote in message
...
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!!!

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

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


"RLind" wrote:

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

"Teethless mama" wrote:

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

ctrl+shift+enter, not just enter
copy down


"RLind" wrote:

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