Page 3 of 3 FirstFirst 123
Results 21 to 26 of 26

Thread: Find next whole number in list

  1. #21
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,254
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Find next whole number in list

    If you want the number of rows between the first real whole number and the second real whole number, you could use this array-entered** formula...

    =SMALL(IF(ISNUMBER(A2:A15),ROW(A2:A15)),2)-SMALL(IF(ISNUMBER(A2:A15),ROW(A2:A15)),1)-1

    **Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  2. #22
    MrExcel MVP Tetra201's Avatar
    Join Date
    Oct 2016
    Posts
    3,395
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Find next whole number in list

    A more compact version of this ^^^ formula was suggested in Post # 11.

  3. #23
    Board Regular
    Join Date
    Aug 2012
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find next whole number in list

    I couldn't get 9 or 10 to work. I don't have a basic understanding of Aggregate to tweak it. THe other switching to my sheet, I could not get it to work. #21 works but it only works for the first and second.
    I don't know how many whole numbers are before the one I am searching for. I need it to find a given Number, find the next integer, and tell me how many rows are in between

  4. #24
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,010
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Find next whole number in list

    Let's throw a couple more into the mix. Basically the same formula but D2 requires Ctrl+Shift+Enter while E2 does not.
    Note that I have changed the sample data slightly as you said
    I don't know how many whole numbers are before the one I am searching for.
    Offset

    ABCDE
    1Id
    2834ename 77
    3666Family
    4834gname
    5835Family
    6832fname
    7473mname
    8835cname
    9835dname
    10835ename
    11835fname
    12839Family
    13832gname
    14346gname
    15836cname
    16921aname
    17922bname
    18888Family
    19923cname

    Spreadsheet Formulas
    CellFormula
    D2{=MATCH(TRUE,ISNUMBER(INDEX(A:A,MATCH(MySearch,A:A,0)+1):A7000),0)}
    E2=MATCH(TRUE,INDEX(ISNUMBER(INDEX(A:A,MATCH(MySearch,A:A,0)+1):A7000),0),0)
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4
    Last edited by Peter_SSs; Sep 13th, 2019 at 08:06 AM. Reason: Typo
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #25
    Board Regular
    Join Date
    Aug 2012
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find next whole number in list

    Thank you. That is a home run. Sorry to all that I was not clearer from the beginning

  6. #26
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,010
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Find next whole number in list

    Quote Originally Posted by ManUBlueJay View Post
    Thank you. That is a home run. Sorry to all that I was not clearer from the beginning
    You're welcome.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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