Is there a function that results/looks for the first single positive (non
zero) value in a Horizontal Array?
For example:
A B C D E F
Row 1: 0 2 5 1 0 2
Return the value that will be the first positive in the array, which will be
"2".

One way:

=INDEX(1:1,MATCH(TRUE,1:10,0))
or
=INDEX(a1:x1,MATCH(TRUE,a1:x10,0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

VCUE wrote:

Is there a function that results/looks for the first single positive (non
zero) value in a Horizontal Array?
For example:
A B C D E F
Row 1: 0 2 5 1 0 2
Return the value that will be the first positive in the array, which will be
"2".

--

Dave Peterson

Try this array formula** :

Assuming the range will only contain numbers.

=IF(COUNTIF(A1:F1,"0"),INDEX(A1:F1,MATCH(TRUE,A1: F10,0)),"")

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

--
Biff
Microsoft Excel MVP


"VCUE" wrote in message
...
Is there a function that results/looks for the first single positive (non
zero) value in a Horizontal Array?
For example:
A B C D E F
Row 1: 0 2 5 1 0 2
Return the value that will be the first positive in the array, which will
be
"2".

Your formula returns 4 instead of 2

=INDEX(1:1,MATCH(TRUE,1:10,0))

ex. a1=4 (text value), b1=2, c1=5, d1=1, e1=0, f1=2



"Dave Peterson" wrote:

One way:

=INDEX(1:1,MATCH(TRUE,1:10,0))
or
=INDEX(a1:x1,MATCH(TRUE,a1:x10,0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

VCUE wrote:

Is there a function that results/looks for the first single positive (non
zero) value in a Horizontal Array?
For example:
A B C D E F
Row 1: 0 2 5 1 0 2
Return the value that will be the first positive in the array, which will be
"2".

--

Dave Peterson

Your formula returns 4 instead of 2

=IF(COUNTIF(A1:F1,"0"),INDEX(A1:F1,MATCH(TRUE,A1: F10,0)),"")

ex. a1=4 (text value), b1=2, c1=5, d1=1, e1=0, f1=2


"T. Valko" wrote:

Try this array formula** :

Assuming the range will only contain numbers.

=IF(COUNTIF(A1:F1,"0"),INDEX(A1:F1,MATCH(TRUE,A1: F10,0)),"")

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

--
Biff
Microsoft Excel MVP


"VCUE" wrote in message
...
Is there a function that results/looks for the first single positive (non
zero) value in a Horizontal Array?
For example:
A B C D E F
Row 1: 0 2 5 1 0 2
Return the value that will be the first positive in the array, which will
be
"2".

=INDEX(1:1,MATCH(1,(1:10)*ISNUMBER(1:1),0))

Still an array formula.

Teethless mama wrote:

Your formula returns 4 instead of 2

=INDEX(1:1,MATCH(TRUE,1:10,0))

ex. a1=4 (text value), b1=2, c1=5, d1=1, e1=0, f1=2

"Dave Peterson" wrote:

One way:

=INDEX(1:1,MATCH(TRUE,1:10,0))
or
=INDEX(a1:x1,MATCH(TRUE,a1:x10,0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

VCUE wrote:

Is there a function that results/looks for the first single positive (non
zero) value in a Horizontal Array?
For example:
A B C D E F
Row 1: 0 2 5 1 0 2
Return the value that will be the first positive in the array, which will be
"2".

--

Dave Peterson


--

Dave Peterson

I think this array-entered** formula will do what you asked...

=INDEX(A1:F1,1,MIN(IF(A1:F1=0,99999,COLUMN(A1:F1)) ))

** Commit this formula using Ctrl+Shift+Enter (not just Enter).

Rick


"VCUE" wrote in message
...
Is there a function that results/looks for the first single positive (non
zero) value in a Horizontal Array?
For example:
A B C D E F
Row 1: 0 2 5 1 0 2
Return the value that will be the first positive in the array, which will
be
"2".

Your formula returns 4 instead of 2
ex. a1=4 (text value), b1=2, c1=5, d1=1, e1=0, f1=2

Apparently you missed this:

Assuming the range will only contain numbers.


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
Your formula returns 4 instead of 2

=IF(COUNTIF(A1:F1,"0"),INDEX(A1:F1,MATCH(TRUE,A1: F10,0)),"")

ex. a1=4 (text value), b1=2, c1=5, d1=1, e1=0, f1=2


"T. Valko" wrote:

Try this array formula** :

Assuming the range will only contain numbers.

=IF(COUNTIF(A1:F1,"0"),INDEX(A1:F1,MATCH(TRUE,A1: F10,0)),"")

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

--
Biff
Microsoft Excel MVP


"VCUE" wrote in message
...
Is there a function that results/looks for the first single positive
(non
zero) value in a Horizontal Array?
For example:
A B C D E F
Row 1: 0 2 5 1 0 2
Return the value that will be the first positive in the array, which
will
be
"2".