Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Formula to find non-text item in a range (XL2003)

    I need a formula in A1 that will return the first non-text 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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula to find non-text 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.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formula to find non-text item in a range (XL2003)

    How about the array (confirm with ctrl-shift-enter):

    =IF(COUNT(B1:B36),INDEX(B1:B36,MIN(IF(ISNUMBER(B1: B36),ROW(B1:B36)))),0)

    Steve

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula to find non-text 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>)

  5. #5
    Silver Lounger
    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

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Formula to pull first non-text 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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula to pull first non-text 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.

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula to pull first non-text 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

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula to pull first non-text 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •