Excel Formula – List Missing Numbers in a Sequence

A little while ago I received an email from Deborah asking what formula she can use to list missing numbers from a range.

I’ve seen loads of different approaches to this over the years but today I’m going to share with you the formula I think is the most elegant.

Here is Deborah’s list of numbers:

You’ll notice they’re not sorted and there is a blank cell in the middle of the range. So, our formula needs to be able to handle blanks and an unsorted list.

And here she is:

=SMALL(IF(COUNTIF($A$1:$A$7,ROW($1:$12))=0,ROW($1:$12),""),ROW(A1))

It’s an array formula so you need to enter it with CTRL+SHIFT+ENTER.

Below you can see the results of the formula copied down column C. When there are no more missing numbers it returns a #NUM! error.

Tip: You can easily avoid the #NUM! errors by wrapping it in an IFERROR function if you prefer.

In English the Formula in Cell C1 Reads:

Count the numbers in the range A1:A7, that match the numbers generated by ROW($1:$12) i.e. {1;2;3;4;5;6;7;8;9;10;11;12}, if they = 0 (i.e. if they’re missing), give me a list of them, if they’re not = 0 (i.e. not missing), give me "" (i.e. nothing), and in this cell just return the 1^st smallest missing value.

The cell reference in the ROW(A1) part of the formula is relative, so as you copy the formula down column C, ROW(A1) becomes ROW(A2) which =2 and returns the second smallest missing number, ROW(A3) which is 3, returns the third smallest missing number and so on.

Functions Used in this Formula

SMALL

The SMALL function returns the smallest k^th value in an array. e.g the first smallest, second smallest, third smallest and so on.

The syntax is SMALL(array,k)

ROW

This ROW formula: ROW(A1) returns the row number of a reference. In this case it would return 1.

This ROW formula: ROW($1:$12) returns an array of values from 1 to 12 like this {1;2;3;4;5;6;7;8;9;10;11;12}. It works this way because this is an array formula entered with CTRL+SHIFT+ENTER.

COUNTIF

The COUNTIF function is counting the numbers in the range A1:A7 that match the array of values returned by the ROW function {1;2;3;4;5;6;7;8;9;10;11;12}.

The syntax is: COUNTIF(range, criteria)

IF

The IF function tests for counts of 0, and if TRUE i.e. zero (remember if the count is zero it means the number is missing), it then returns the corresponding value from the resultant array generated by ROW($1:$12) i.e. {1;2;3;4;5;6;7;8;9;10;11;12}, otherwise it returns nothing.

The syntax is: IF(logical_test, value_if_true, value_if_false)

Let’s Step Through How It Evaluates:

The key here is that array formulas evaluate on an array of items i.e. more than one item, within the one formula. Below you will see evidence of this as the formula evaluates.

=SMALL(IF(COUNTIF($A$1:$A$7,ROW($1:$12))=0,ROW($1:$12),""),ROW(A1))

Step 1 - The values in the range A1:A7 and ROW(1:12) are returned:

=SMALL(IF(COUNTIF({1;7;5;8;;10;12},{1;2;3;4;5;6;7;8;9;10;11;12})=0,ROW($1:$12),""),ROW(A1))

Step 2 - The COUNTIF then returns (a resultant array of) the counts of values in the array returned by the ROW(1:12) formula that are present in the range A1:A7:

=SMALL(IF({1;0;0;0;1;0;1;1;0;1;0;1})=0, ROW($1:$12),""),ROW(A1))

i.e. in the range A1:A7 there is 1 number 1 in the range, there are 0 number 2’s, 0 number 3’s etc.

Step 3 – The IF function’s logical test evaluates returning a TRUE where the number in the resultant array =0, and a FALSE where it doesn’t:

=SMALL(IF({FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE}, {1;2;3;4;5;6;7;8;9;10;11;12},""),ROW(A1))

Step 4 – the IF function’s value_if_true and value_if_false evaluate:

=SMALL({"";2;3;4; "";6;"";"";9; "";11;""},ROW(A1))

We now have a resultant array of the missing numbers.

Step 5 – The final ROW function evaluates to 1:

=SMALL({"";2;3;4; "";6;"";"";9; "";11;""},1)

Step 6 – the SMALL function finds the 1^st smallest value in the array:

=2

Tip: the ROW function is used as a short cut to avoid having to type in an array constant. i.e. it’s quicker to type ROW($1:$12) than it is to type {1;2;3;4;5;6;7;8;9;10;11;12}

Note: This formula will ignore duplicate values in the list since it’s only looking for numbers in the range that have a count of 0, i.e. are missing.

Limitations of This Formula:

1. It uses the ROW function to generate a list of values that should be in Deborah’s range. It is therefore limited to the number of rows in your workbook. If you’re using Excel 2003 you’re limited to numbers in the range 1 to 65,536, and if you’re using Excel 2007 onwards you’re limited to number between 1 and 1,048,576.
2. It only works with positive numbers >0.
3. It only works with whole numbers/integers.

Here is a clever VBA solution to find missing numbers that gets around some of the limitations above, plus the added bonus of allowing you to choose whether you want your list of missing values returned in a vertical or horizontal list.

Formula Challenge

What if your sequence of numbers were all negative. What formula would you use to find the missing negative numbers?

Please leave your answer in the comment field below.

Thanks

I’d like to thank Deborah for asking this question. It reminded me that I should write about this formula.

I’d also like to thank Oscar Cronquist of Get Digital Help for sharing his formula.

If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+1, Facebook and Twitter.