# Find next whole number in list

#### Rick Rothstein

##### MrExcel MVP
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

### 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
A more compact version of this ^^^ formula was suggested in Post # 11.

#### ManUBlueJay

##### Board Regular
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
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:

#### ManUBlueJay

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

#### Peter_SSs

##### MrExcel MVP, Moderator
Thank you. That is a home run. Sorry to all that I was not clearer from the beginning
You're welcome.

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