A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Reload this Page How can I use a formula to return the first number in a range?
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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


« Previous Thread | Next Thread »

 
Page 1 of 2 1 2
 
Thread Tools Display Modes
  #1  
Old June 14th, 2009, 03:30 PM posted to microsoft.public.excel.worksheet.functions
Husker87
external usenet poster
  
Posts: 23
Default How can I use a formula to return the first number in a range?
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??
  #2  
Old June 14th, 2009, 04:12 PM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
  
Posts: 5,952
Default 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:A100,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. 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??
  #3  
Old June 14th, 2009, 05:25 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
  
Posts: 15,759
Default 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??


  #4  
Old June 14th, 2009, 06:10 PM posted to microsoft.public.excel.worksheet.functions
RagDyeR
external usenet poster
  
Posts: 3,482
Default How can I use a formula to return the first number in a range?
Non-array suggestions:

=INDEX(A1:A10,MATCH(TRUE,INDEX(A1:A100,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. 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??
  #5  
Old June 14th, 2009, 06:25 PM posted to microsoft.public.excel.worksheet.functions
Shane Devenshire[_3_]
external usenet poster
  
Posts: 3,333
Default 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. 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??
  #6  
Old June 14th, 2009, 06:29 PM posted to microsoft.public.excel.worksheet.functions
Shane Devenshire[_3_]
external usenet poster
  
Posts: 3,333
Default 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. 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??
  #7  
Old June 14th, 2009, 06:39 PM posted to microsoft.public.excel.worksheet.functions
Shane Devenshire[_3_]
external usenet poster
  
Posts: 3,333
Default 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. 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??
  #8  
Old June 14th, 2009, 06:41 PM posted to microsoft.public.excel.worksheet.functions
Husker87
external usenet poster
  
Posts: 23
Default 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:A100,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. 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??
  #9  
Old June 14th, 2009, 06:59 PM posted to microsoft.public.excel.worksheet.functions
Shane Devenshire[_3_]
external usenet poster
  
Posts: 3,333
Default 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:A100,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. 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??
  #10  
Old June 14th, 2009, 10:10 PM posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_]
external usenet poster
  
Posts: 1,439
Default 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))
 
Page 1 of 2 1 2



« Previous Thread | Next Thread »

Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:43 AM.

Feed Icon - Contact Us - OfficeFrustration excel & word help home - FAQ - Links - Privacy Statement - Top

Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.