Results 1 to 9 of 9

20060205, 04:33 #1
 Join Date
 Mar 2001
 Location
 Dallas, Texas, USA
 Posts
 1,680
 Thanks
 0
 Thanked 1 Time in 1 Post
Formula to find nontext item in a range (XL2003)
I need a formula in A1 that will return the first nontext amount in a range (B1:B36),
B1 thru B36 is filled with formulae: =if(isblank(cell),"NO",cell). So B1:B36 will either have a number or the text "NO". If it has a number, it could be negative, zero or positive. I need the formula in A1 that will search down the B column and return the first number it finds (going top to bottom).
Lastly, if the entire range is filled with 36 "NO"s, the formula should return a zero. Ricky

20060205, 11:48 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Formula to find nontext item in a range (XL2003)
If the formulas in B1:B36 refer to a contiguous range, say C1:C36, it would be easier to refer to that range than to B1:B36. The following array formula (confirm with Ctrl+Shift+Enter) will do what you want:
=INDEX(C1:C36,MIN(IF(ISNUMBER(C1:C36)*ROW(1:36)>0, ISNUMBER(C1:C36)*ROW(1:36))))
If you prefer to use B1:B36:
=IF(MIN(IF(ISNUMBER(B1:B36)*ROW(1:36)>0,ISNUMBER(B 1:B36)*ROW(1:36)))=0,0,INDEX(B1:B36,MIN(IF(ISNUMBE R(B1:B36)*ROW(1:36)>0,ISNUMBER(B1:B36)*ROW(1:36))) ))
If suppose someone will come up with a much simpler formula.

20060205, 12:26 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Formula to find nontext item in a range (XL2003)
How about the array (confirm with ctrlshiftenter):
=IF(COUNT(B1:B36),INDEX(B1:B36,MIN(IF(ISNUMBER(B1: B36),ROW(B1:B36)))),0)
Steve

20060205, 12:36 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Formula to find nontext item in a range (XL2003)
That's a lot shorter! (I have a cold, and all I could come up with was complicated... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)

20060206, 08:21 #5
 Join Date
 Mar 2001
 Location
 Dallas, Texas, USA
 Posts
 1,680
 Thanks
 0
 Thanked 1 Time in 1 Post
Thank you very much.
Thanks Steve and Hans for the replies. I used the array offered by Steve and it works fine.
<img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> Ricky

20060207, 15:58 #6
 Join Date
 Mar 2001
 Location
 Dallas, Texas, USA
 Posts
 1,680
 Thanks
 0
 Thanked 1 Time in 1 Post
Formula to pull first nontext entry from a column
Upon further testing, I've discovered that the formulas offered didn't quite work as I hoped. Probably something I did! <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
Anyway, I've attached a sample worksheet with the problems highlighted. The formulas seem to be pulling the last numerical entry in the columns rather than the first. The specific formulas I need help with are in cells p39:u39 on sheet "001". Ricky

20060207, 16:13 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Formula to pull first nontext entry from a column
Try this array formula in 001!P39:
=IF(COUNT(P2:P37),INDEX(P2:P37,MIN(IF(ISNUMBER(P2: P37),ROW(1:36)))),0)
The 1:36 is independent of the actual location of the cells.

20060207, 16:22 #8
 Join Date
 Mar 2001
 Location
 Dallas, Texas, USA
 Posts
 1,680
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Formula to pull first nontext entry from a co
Hans, Thanks for the quick solution. It works fine.
I had gone back and tried the formula you originally gave me in <post#=554707>post 554707</post#> but I did change the 1:36 to 2:37. Just looked like the right thing to do.... <img src=/S/smile.gif border=0 alt=smile width=15 height=15> Ricky

20060207, 16:25 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Formula to pull first nontext entry from a co
No, it's used as an index into the range of cells. The first cell of a range is always no 1, whether the range is B1:B36, P9:P1200 or W29039:W34195.