|
| 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. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#11
June 15th, 2009, 02:51 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) 
|
|
#12
June 15th, 2009, 02:53 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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: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??
|
|
#13
June 15th, 2009, 02:57 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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))
|
|
#14
June 15th, 2009, 06:57 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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?
|
|
#15
June 16th, 2009, 03:26 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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?
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
