Calculating missing numbers



I have a list of numbers from 1 to 1000 in Column A. Some of the numbers are
missing; for example they may run 1, 2 3, 7 etc.

I need a formula that will calculate the missing numbers and place them in
Column B.

Many thanks in advance.

Bernie Deitrick


In cell B1, enter the formula


and copy down to B2:B1000.

Copy all of B, paste special values, and then sort B ascending, and you will have your list.

MS Excel MVP

Bernard Liengme

In A1 enter =IF(COUNTIF(A:A,ROW(A1)),"",ROW(A1))
Copy down the column
Missing numbers will be displayed
The column will have empty cells; use Copy followed by Paste Special->Vales
to turn formulas to values
Now you can sort the column to get rid of blanks
best wishes

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question