how can we find out any missed number in a series?

N

Niek Otten

If your numbers are in column A, enter this formula in B2:

=IF(A2-A1=1,"",A2-A1-1)

and copy down as far as needed

--
Kind regards,

Niek Otten

|
 
A

Ardus Petus

Assuming your series is in range A1:A999
Enter following formula in B2
=A2=A1+1

All FALSE results will mark the places where there are missing numbers

HTH
 
N

Niek Otten

This one indicates how many there are missing

--
Kind regards,

Niek Otten

| If your numbers are in column A, enter this formula in B2:
|
| =IF(A2-A1=1,"",A2-A1-1)
|
| and copy down as far as needed
|
| --
| Kind regards,
|
| Niek Otten
|
||
|
|
 
R

Ron Coderre

With your list of values in Col_A (eg 1,2,5,6,10,11,15,20,21,25)

This ARRAY FORMULA* lists the missing items in ascending order:

B1:
=INDEX(ROW($A$1:INDEX(A:A,MAX(A:A))),SMALL(IF(COUNTIF($A$1:INDEX(A:A,MAX(A:A)),ROW($A$1:INDEX(A:A,MAX(A:A)))+MIN(A:A)-1)=0,ROW($A$1:INDEX(A:A,MAX(A:A)))+MIN(A:A)-1),ROWS($1:1))-MIN(A:A))+MIN(A:A)

Copy B1 and paste it into B2 and down as far as you need

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Does that help?

Regards,
Ron
 
Top