How can I use a formula to return the first number in a range?
 

I have a range A1:A10. Im looking for a formula to put in B1 that will
return the first number in my range A1:A10 when searching from top to bottom.
Sometime the first number appears in A1 and sometimes not until A4 with
other numbers in A7 and A8. I only want A4 or the first number. Thoughts??

How can I use a formula to return the first number in a range?
 

To return the first number
=INDEX(A1:A10,MIN(IF(A1:A10<"",ROW(A1:A10))))


To return the 1st non zero number
=INDEX(A1:A10,MIN(IF(A1:A10<0,ROW(A1:A10))))

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

If this post helps click Yes
---------------
Jacob Skaria


"Husker87" wrote:

I have a range A1:A10. Im looking for a formula to put in B1 that will
return the first number in my range A1:A10 when searching from top to bottom.
Sometime the first number appears in A1 and sometimes not until A4 with
other numbers in A7 and A8. I only want A4 or the first number. Thoughts??

How can I use a formula to return the first number in a range?
 

Try this array formula** :

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

--
Biff
Microsoft Excel MVP


"Husker87" wrote in message
...
I have a range A1:A10. I'm looking for a formula to put in B1 that will
return the first number in my range A1:A10 when searching from top to
bottom.
Sometime the first number appears in A1 and sometimes not until A4 with
other numbers in A7 and A8. I only want A4 or the first number.
Thoughts??

How can I use a formula to return the first number in a range?
 

Non-array suggestions:

=INDEX(A1:A10,MATCH(TRUE,INDEX(A1:A10<0,0),0))

=INDEX(A1:A10,MATCH(TRUE,INDEX(A1:A100,0),0))

Depending on what you're looking for.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Husker87" wrote in message
...
I have a range A1:A10. Im looking for a formula to put in B1 that will
return the first number in my range A1:A10 when searching from top to
bottom.
Sometime the first number appears in A1 and sometimes not until A4 with
other numbers in A7 and A8. I only want A4 or the first number.
Thoughts??

How can I use a formula to return the first number in a range?
 

Hi,

Here is an array formula:

=INDEX(A1:A10,MATCH(1,--(A1:A10*10),0))
or
=INDEX(A1:A10,MATCH(TRUE,A1:A10*10,0))

To make it an array you enter it by pressing Shift+Ctr+Enter

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Husker87" wrote:

I have a range A1:A10. Im looking for a formula to put in B1 that will
return the first number in my range A1:A10 when searching from top to bottom.
Sometime the first number appears in A1 and sometimes not until A4 with
other numbers in A7 and A8. I only want A4 or the first number. Thoughts??

How can I use a formula to return the first number in a range?
 

I should add that this returns the first non-zero number.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Shane Devenshire" wrote:

Hi,

Here is an array formula:

=INDEX(A1:A10,MATCH(1,--(A1:A10*10),0))
or
=INDEX(A1:A10,MATCH(TRUE,A1:A10*10,0))

To make it an array you enter it by pressing Shift+Ctr+Enter

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Husker87" wrote:

I have a range A1:A10. Im looking for a formula to put in B1 that will
return the first number in my range A1:A10 when searching from top to bottom.
Sometime the first number appears in A1 and sometimes not until A4 with
other numbers in A7 and A8. I only want A4 or the first number. Thoughts??

How can I use a formula to return the first number in a range?
 

A little bit more -

For the first number (including 0)

=INDEX(A1:A10,MATCH(1,--ISNUMBER(A1:A10),))

and you can shorten my previous formula a tad for the non-zero situation

=INDEX(A1:A10,MATCH(1,--(A1:A10*10),))

both are array entered.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Shane Devenshire" wrote:

Hi,

Here is an array formula:

=INDEX(A1:A10,MATCH(1,--(A1:A10*10),0))
or
=INDEX(A1:A10,MATCH(TRUE,A1:A10*10,0))

To make it an array you enter it by pressing Shift+Ctr+Enter

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Husker87" wrote:

I have a range A1:A10. Im looking for a formula to put in B1 that will
return the first number in my range A1:A10 when searching from top to bottom.
Sometime the first number appears in A1 and sometimes not until A4 with
other numbers in A7 and A8. I only want A4 or the first number. Thoughts??

How can I use a formula to return the first number in a range?
 

THANKS. That worked. Have a great day.

"Ragdyer" wrote:

Non-array suggestions:

=INDEX(A1:A10,MATCH(TRUE,INDEX(A1:A10<0,0),0))

=INDEX(A1:A10,MATCH(TRUE,INDEX(A1:A100,0),0))

Depending on what you're looking for.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Husker87" wrote in message
...
I have a range A1:A10. Im looking for a formula to put in B1 that will
return the first number in my range A1:A10 when searching from top to
bottom.
Sometime the first number appears in A1 and sometimes not until A4 with
other numbers in A7 and A8. I only want A4 or the first number.
Thoughts??

How can I use a formula to return the first number in a range?
 

Hi,

I think both of these suggestions will return the first text entry if there
is one before the first number.

Here are two non-array solution which avoid text entries:

First Non-Zero number - non array:
=INDEX(A1:A10,MATCH(TRUE,INDEX(A1:A10*10,),))

First Number - non array:
=INDEX(A1:A10,MATCH(TRUE,INDEX(ISNUMBER(A1:A10),), ))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Ragdyer" wrote:

Non-array suggestions:

=INDEX(A1:A10,MATCH(TRUE,INDEX(A1:A10<0,0),0))

=INDEX(A1:A10,MATCH(TRUE,INDEX(A1:A100,0),0))

Depending on what you're looking for.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Husker87" wrote in message
...
I have a range A1:A10. Im looking for a formula to put in B1 that will
return the first number in my range A1:A10 when searching from top to
bottom.
Sometime the first number appears in A1 and sometimes not until A4 with
other numbers in A7 and A8. I only want A4 or the first number.
Thoughts??

How can I use a formula to return the first number in a range?
 

"T. Valko" wrote...
Try this array formula** :

=INDEX(A1:A10,MATCH(TRUE,ISNUMBER(A1:A10),0))
....

Could avoid array entry with

=INDEX(A1:A20,MATCH(TRUE,INDEX(ISNUMBER(A1:A20),0) ,0))

How can I use a formula to return the first number in a range?
 

Besides bypassing text, why not add the other alternative of *also*
bypassing zeroes:

=INDEX(A1:A20,MATCH(1,INDEX((ISNUMBER(A1:A20))*(A1 :A100),),))

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Harlan Grove" wrote in message
...
"T. Valko" wrote...
Try this array formula** :

=INDEX(A1:A10,MATCH(TRUE,ISNUMBER(A1:A10),0))
...

Could avoid array entry with

=INDEX(A1:A20,MATCH(TRUE,INDEX(ISNUMBER(A1:A20),0) ,0))

How can I use a formula to return the first number in a range?
 

You're welcome, and appreciate the feed-back.

Hope you see all the other options your question has generated.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Husker87" wrote in message
...
THANKS. That worked. Have a great day.

"Ragdyer" wrote:

Non-array suggestions:

=INDEX(A1:A10,MATCH(TRUE,INDEX(A1:A10<0,0),0))

=INDEX(A1:A10,MATCH(TRUE,INDEX(A1:A100,0),0))

Depending on what you're looking for.
--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Husker87" wrote in message
...
I have a range A1:A10. Im looking for a formula to put in B1 that
will
return the first number in my range A1:A10 when searching from top to
bottom.
Sometime the first number appears in A1 and sometimes not until A4
with
other numbers in A7 and A8. I only want A4 or the first number.
Thoughts??

How can I use a formula to return the first number in a range?
 

TYPO - must equalize the ranges!

=INDEX(A1:A10,MATCH(1,INDEX((ISNUMBER(A1:A10))*(A1 :A100),),))

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" wrote in message
...
Besides bypassing text, why not add the other alternative of *also*
bypassing zeroes:

=INDEX(A1:A20,MATCH(1,INDEX((ISNUMBER(A1:A20))*(A1 :A100),),))

--
Regards,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-

"Harlan Grove" wrote in message
...
"T. Valko" wrote...
Try this array formula** :

=INDEX(A1:A10,MATCH(TRUE,ISNUMBER(A1:A10),0))
...

Could avoid array entry with

=INDEX(A1:A20,MATCH(TRUE,INDEX(ISNUMBER(A1:A20),0) ,0))

How can I use a formula to return the first number in a range?
 

"Ragdyer" wrote...
Besides bypassing text, why not add the other alternative of *also*
bypassing zeroes:
....

Since when is zero not a number? Since when are negative numbers not
nonzero numbers?

Where did the OP give any hint that s/he only wanted positive numbers?

How can I use a formula to return the first number in a range?
 

Strictly an academic exercise Harlan.

The archives could benefit ... no?

---

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------



"Harlan Grove" wrote in message
...
"Ragdyer" wrote...
Besides bypassing text, why not add the other alternative of *also*
bypassing zeroes:
....

Since when is zero not a number? Since when are negative numbers not
nonzero numbers?

Where did the OP give any hint that s/he only wanted positive numbers?