How to List Missing Numbers In a Sequence

Missing numbers in a sequence can lead to wrong results in data analysis, especially if you are using these numbers to keep a count of items.

In the last article, we showed How to Highlight Non-Sequential Numbers. This article shows how to list the missing numbers

Simple Missing Numbers in a Sequence

Which orders are missing below?

NB: Orders list is unsorted and has gaps in between

{=SMALL(IF(COUNTIF(tblOrders[Order ‘#],ROW($1:$19))=0,ROW($1:$19),””),ROW(A1))}

Step:

1. Determine the Maximum and Minimum order numbers. This will be used to spill the complete sequence
2. Use the ROW function to generate an array of complete order series
3. Use the COUNTIF function to count for missing numbers in the series. 1=present, 0=missing
4. Use the IF function to test for 0 counts and return the corresponding value in the array returned by ROW.
5. Use the SMALL function to find the values from smallest to largest

The alternative to hardcoding the max and min values in the ROW function is using the INDIRECT function

{=SMALL(IF(COUNTIF(tblOrders[Order ‘#],ROW(INDIRECT($G$2&”:”&$G$3)))=0,ROW(INDIRECT($G$2&”:”&$G$3)),””),ROW(A1))}

This alternative offers more flexibility in your formula.

Complex Missing Numbers in a Sequence

One of the limit in the above formula is you are limited by the range of rows in excel between 1 and 1,048,576.

So, how do you find missing numbers in series below?

=FILTER(NOT(COUNTIF(B3:B13,SEQUENCE(MAX(B3:B13)+1-MIN(B3:B13),,MIN(B3:B13))))*SEQUENCE(MAX(B3:B13)+1-MIN(B3:B13),,MIN(B3:B13)),NOT(COUNTIF(B3:B13,SEQUENCE(MAX(B3:B13)+1-MIN(B3:B13),,MIN(B3:B13)))))

How it works

⭐ SEQUENCE(MAX(B3:B13)+1-MIN(B3:B13),,MIN(B3:B13)) spills an array for complete sequence from the smallest to largest number

⭐ COUNTIF(B3:B13,SEQUENCE(MAX(B3:B13)+1-MIN(B3:B13),,MIN(B3:B13))) Counts the order numbers present in the spilled array above. Returns 1 if present and 0 if misssing

⭐ NOT(COUNTIF(B3:B13,SEQUENCE(MAX(B3:B13)+1-MIN(B3:B13),,MIN(B3:B13)))) reverses the above result with 1 being missing and 0 present

⭐ NOT(COUNTIF(B3:B13,SEQUENCE(MAX(B3:B13)+1-MIN(B3:B13),,MIN(B3:B13)))) * SEQUENCE(MAX(B3:B13)+1-MIN(B3:B13),,MIN(B3:B13)) when you multiply the array returned by NOT function with the complete series array as spilled by SEQUENCE function, you get the missing numbers and zeros

All that is remaining is to filter out the zeros using the Filter function

“Text-Numbers” Missing Numbers in a Sequence

What if your series is a mix of numbers and text like the below?

Step 1: Extract the numbers from the text using the RIGHT function

=RIGHT(IF(ISBLANK(tblStaff[Emp No.]),0,tblStaff[Emp No.]),3)+0

NB:

• +0 is for converting the text-number extracted by the RIGHT function to a real number
• You have to check for blanks in your series else it will result in a #VALUE error when you try to convert the text array to numbers

Step 2: Find the Smallest numbers

=SMALL(RIGHT(IF(ISBLANK(tblStaff[Emp No.]),0,tblStaff[Emp No.]),3)+0,2)

NB:

• You cannot use MIN function as it will always result in 0 as the minimum yet this represents the blank in the series
• Find the second smallest using the SMALL function

Step 3: Find the largest number

=MAX(RIGHT(IF(ISBLANK(tblStaff[Emp No.]),0,tblStaff[Emp No.]),3)+0)

Step 4: Spill an array of complete series using SEQUENCE

SEQUENCE( I2+1-G2,,G2)

where I2 is the cell reference for maximum number and G2 for a minimum number

Step 5: Counts the Emp No. numbers present in the spilled array above. Returns 1 if present and 0 if missing

COUNTIF( E3#, SEQUENCE(I2+1-G2,,G2) )

Step 6: Use IF function to test for 0 count and return corresponding value in the array returned by SEQUENCE function

IF(COUNTIF(E3#,SEQUENCE(I2+1-G2,,G2))=0,SEQUENCE(I2+1-G2,,G2),””)

Step 7: Filter out the spaces or false values

=FILTER(IF(COUNTIF(E3#,SEQUENCE(I2+1-G2,,G2))=0,SEQUENCE(I2+1-G2,,G2),””),COUNTIF(E3#,SEQUENCE(I2+1-G2,,G2))=0)

Negative Numbers Missing Numbers in a Sequence

How about negative numbers series?

Step 1: Use SEQUENCE function to spill array of full negative series

=SEQUENCE(MAX(B3:B13)+1-MIN(B3:B13),,MAX(B3:B13),-1)

Step 2: Counts the negative series numbers present in the spilled array above. Returns 1 if present and 0 if misssing

=COUNTIF(B3:B13,SEQUENCE(MAX(B3:B13)+1-MIN(B3:B13),,MAX(B3:B13),-1))

Step 3: Use IF function to test for 0 count and return corresponding value in the array returned by SEQUENCE function

=IF(COUNTIF(B3:B13,SEQUENCE(MAX(B3:B13)+1-MIN(B3:B13),,MAX(B3:B13),-1))=0,SEQUENCE(MAX(B3:B13)+1-MIN(B3:B13),,MAX(B3:B13),-1),””)

Step 4 Filter out spaces

=FILTER(IF(COUNTIF(B3:B13,SEQUENCE(MAX(B3:B13)+1-MIN(B3:B13),,MAX(B3:B13),-1))=0,SEQUENCE(MAX(B3:B13)+1-MIN(B3:B13),,MAX(B3:B13),-1),””),COUNTIF(B3:B13,SEQUENCE(MAX(B3:B13)+1-MIN(B3:B13),,MAX(B3:B13),-1))=0)

Conclusion:

You can you the same process to list any numerical series like Dates and times too.

Thanks to MYNDA TREACY for her article on Excel Formula – List Missing Numbers in a Sequence

DOWNLOAD WORKSHEET