Is there a formula to identify missing numbers in a list of consecutive
numbers?
--
Peace squared

Assume numbers are listed in A1:A3, eg: 1,3,6
Assume the range of numbers to check is 10 numbers, ie: 1-10
In B1: =IF(ISNUMBER(MATCH(ROWS($1:1),A:A,0)),"",ROWS($1:1 ))
In C1: =IF(ROWS($1:1)COUNT(B:B),"",INDEX(B:B,SMALL(B:B,R OWS($1:1))))
Copy B1:C1 down the extent, ie by 10 rows to C10. Col C will return the
required results, ie the missing numbers within the range, all neatly bunched
at the top. Adapt to suit.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,400 Files:356 Subscribers:53
xdemechanik
---
"Lj" wrote:
Is there a formula to identify missing numbers in a list of consecutive
numbers?
--
Peace squared

One way...

Assumptions:

A1:A50 = range of numbers
The sequence to check against is 1 to 100

Try this array formula** :

=SMALL(IF(ISNA(MATCH(ROW(A$1:A$100),A$1:A$50,0)),R OW(A$1:A$100)),ROW(A1))

Copy down until you get #NUM! errors meaning all missing numbers have been
listed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

This method is slow to calculate on very large sequences.



--
Biff
Microsoft Excel MVP


"Lj" wrote in message
...
Is there a formula to identify missing numbers in a list of consecutive
numbers?
--
Peace squared

Hi Lj

If so, you could try this, array-entered,
ie press CTRL+SHIFT+ENTER to confirm the formula:

=SMALL(IF(ISNA(MATCH(ROW(INDIRECT(MIN(x)&":"&MAX(x ))),x,0))
,ROW(INDIRECT(MIN(x)&":"&MAX(x))),""),ROW(INDIRECT ("1:"&(MAX(x)-MIN(x)-COUNT(x)+1))))

where x is a rang of dates

http://tinyurl.com/5nv7vp

Regards,
Pedro J.

Is there a formula to identify missing numbers in a list of consecutive
numbers?

Thanx Max, quick reply, only thing.....
Column C remains blank.
Let me elaborate, this is a list of cheques. The ist chq.# is 929 the last
is 1667. Do I substitute ($929:1) for ($1:1) or($1:929). That may be where my
problem lies.
--
Peace squared


"Max" wrote:

Assume numbers are listed in A1:A3, eg: 1,3,6
Assume the range of numbers to check is 10 numbers, ie: 1-10
In B1: =IF(ISNUMBER(MATCH(ROWS($1:1),A:A,0)),"",ROWS($1:1 ))
In C1: =IF(ROWS($1:1)COUNT(B:B),"",INDEX(B:B,SMALL(B:B,R OWS($1:1))))
Copy B1:C1 down the extent, ie by 10 rows to C10. Col C will return the
required results, ie the missing numbers within the range, all neatly bunched
at the top. Adapt to suit.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,400 Files:356 Subscribers:53
xdemechanik
---
"Lj" wrote:
Is there a formula to identify missing numbers in a list of consecutive
numbers?
--
Peace squared

Try it this way ..

Source numbers (assumed real numbers) in A1 down

In B1:
=IF(ISNUMBER(MATCH(ROWS($1:1)+928,A:A,0)),"",ROWS( $1:1)+928)
(Add 928 to ROWS($1:1) so that it begins with 929 - your 1st number)

In C1:
=IF(ROWS($1:1)COUNT(B:B),"",SMALL(B:B,ROWS($1:1)) )
Copy B1:C1 down by 739* rows to C739
*the range: 929 to 1667 contains 1667-929+1 = 739 numbers
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,400 Files:356 Subscribers:53
xdemechanik
---
"Lj" wrote:
Thanx Max, quick reply, only thing.....
Column C remains blank.
Let me elaborate, this is a list of cheques. The ist chq.# is 929 the last
is 1667. Do I substitute ($929:1) for ($1:1) or($1:929). That may be where my
problem lies.
--
Peace squared