Show the first number in line ?

[baramburum]

Dear Experts,

I need a formula for cell to Show the First Number in Line

I have a line with blank cells in the beginning and numbers after. I need to show in my cell the number in the first cell after blank ones.

Please help with formula.

Thanks

 

[T. Valko]

Dear Experts,

I need a formula for cell to Show the First Number in Line

I have a line with blank cells in the beginning and numbers after. I need to show in my cell the number in the first cell after blank ones.

Please help with formula.

Thanks

Try this...

Book1
	A	B	C	D	E	F
1			37	11	15	11
Sheet1

Array entered**:

=INDEX(A1:F1,MATCH(TRUE,ISNUMBER(A1:F1),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.

 

[baramburum]

T. Valko, thank you so much

 

[T. Valko]

T. Valko, thank you so much

You're welcome. Thanks for the feedback!

 

[baramburum]

Hi Valko again,

How to do the same, but to show the "FIRST POSITIVE" number in line ?

 

[Aladin Akyurek]

Hi Valko again,

How to do the same, but to show the "FIRST POSITIVE" number in line ?

Control+shift+enter, not just enter:

=INDEX(A1:F1,MATCH(1,IF(ISNUMBER(A1:F1),IF(A1:F1>0,1)),0))

 

[baramburum]

Control+shift+enter, not just enter:

=INDEX(A1:F1,MATCH(1,IF(ISNUMBER(A1:F1),IF(A1:F1>0,1)),0))

Aladin, works well.
Thanks

 

[Aladin Akyurek]

Aladin, works well.
Thanks

You are welcome. Thanks for providing feedback.

 

[T. Valko]

Hi Valko again,

How to do the same, but to show the "FIRST POSITIVE" number in line ?

Assuming the range will contain only numbers.

How should 0 be evaluated? Is it a positive number?

Try this. This is based on positive numbers being greater than 0.

Book1
	A	B	C	D	E	F
1	_	-5	0	-1	3	5
Sheet1

Both formulas are array formulas**.

=INDEX(A1:F1,MATCH(TRUE,A1:F1>0,0))

If you need or want an error trap:

=IF(COUNTIF(A1:F1,">0"),INDEX(A1:F1,MATCH(TRUE,A1:F1>0,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.