Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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?? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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?? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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?? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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?? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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?? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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?? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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?? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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?? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
#11
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
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: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?? |
#13
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
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
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
return next highest number in range | Excel Worksheet Functions | |||
Return the minimum number in a range excluding zero | Excel Worksheet Functions | |||
Return Result If Number Is Within Range | Excel Worksheet Functions | |||
how can I return a value in excel that looks at a range of number | Excel Worksheet Functions | |||
how to return a certain value if a number is within a range | Excel Discussion (Misc queries) |