Find next whole number in list

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,652
Office Version
2010
Platform
Windows
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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,490
A more compact version of this ^^^ formula was suggested in Post # 11.
 

ManUBlueJay

Board Regular
Joined
Aug 30, 2012
Messages
158
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,523
Office Version
365
Platform
Windows
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.
Excel Workbook
ABCDE
1Id
2834ename77
3666Family
4834gname
5835Family
6832fname
7473mname
8835cname
9835dname
10835ename
11835fname
12839Family
13832gname
14346gname
15836cname
16921aname
17922bname
18888Family
19923cname
Offset
 
Last edited:

Forum statistics

Threads
1,089,599
Messages
5,409,207
Members
403,256
Latest member
Viq

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top