First non blank number in a row
 

I've been trying to figure out how i can identify the first non blank cell in
a series and show me which column that cell is in. e.g.
Col1 Col2 Col3 Col4 Col5
Row1 34.2 3.45 45.1 432.1
Row2 43.1 541
Row3 34.1 51.12

What formula can i key in Col6, that shows me the for row2,that col2 is the
first non blank number, and for row 3, col3 is the first non blank number and
similarly for Row1, col1 is the first non blank number.

Thanks

In F2

=MIN(IF(A2:E2<"",COLUMN(A2:E2)))

which is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rajiv@Ivey" wrote in message
...
I've been trying to figure out how i can identify the first non blank cell
in
a series and show me which column that cell is in. e.g.
Col1 Col2 Col3 Col4 Col5
Row1 34.2 3.45 45.1 432.1
Row2 43.1 541
Row3 34.1 51.12

What formula can i key in Col6, that shows me the for row2,that col2 is
the
first non blank number, and for row 3, col3 is the first non blank number
and
similarly for Row1, col1 is the first non blank number.

Thanks

You did mention that the result should be in colmn 6.
As long as you do not need to add more columns a nested if formula will do
it.

=IF(A10,1,IF(B10,2,IF(C10,3,IF(D10,4,IF(E10,5 ,"none")))))

--
Greetings from New Zealand
Bill K


"Rajiv@Ivey" wrote in message
...
I've been trying to figure out how i can identify the first non blank cell
in
a series and show me which column that cell is in. e.g.
Col1 Col2 Col3 Col4 Col5
Row1 34.2 3.45 45.1 432.1
Row2 43.1 541
Row3 34.1 51.12

What formula can i key in Col6, that shows me the for row2,that col2 is
the
first non blank number, and for row 3, col3 is the first non blank number
and
similarly for Row1, col1 is the first non blank number.

Thanks

Another way

=MATCH(TRUE,A2:D2<"",0)


entered with ctrl + shift & enter


regards,

Peo Sjoblom

"Rajiv@Ivey" wrote:

I've been trying to figure out how i can identify the first non blank cell in
a series and show me which column that cell is in. e.g.
Col1 Col2 Col3 Col4 Col5
Row1 34.2 3.45 45.1 432.1
Row2 43.1 541
Row3 34.1 51.12

What formula can i key in Col6, that shows me the for row2,that col2 is the
first non blank number, and for row 3, col3 is the first non blank number and
similarly for Row1, col1 is the first non blank number.

Thanks

excellent options

Thanks Bob and Peo

Bill K
"Peo Sjoblom" wrote in message
...
Another way

=MATCH(TRUE,A2:D2<"",0)


entered with ctrl + shift & enter


regards,

Peo Sjoblom

"Rajiv@Ivey" wrote:

I've been trying to figure out how i can identify the first non blank
cell in
a series and show me which column that cell is in. e.g.
Col1 Col2 Col3 Col4 Col5
Row1 34.2 3.45 45.1 432.1
Row2 43.1 541
Row3 34.1 51.12

What formula can i key in Col6, that shows me the for row2,that col2 is
the
first non blank number, and for row 3, col3 is the first non blank number
and
similarly for Row1, col1 is the first non blank number.

Thanks