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

[Excel]

[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

"Excel" <[email protected]> wrote in message news:[email protected]...
|

[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
--
AP

"Excel" <[email protected]> a écrit dans le message de news:
[email protected]...
>

[Niek Otten]

This one indicates how many there are missing

--
Kind regards,

Niek Otten

"Niek Otten" <[email protected]> wrote in message news:[email protected]...
| 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
|
| "Excel" <[email protected]> wrote in message news:[email protected]...
||
|
|

[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