Table of Contents
Identifying missing numbers in a sequence using Excel is a simple and efficient process. First, arrange the sequence of numbers in ascending or descending order in a column. Then, use the “Fill Series” feature to automatically fill in any missing numbers in the sequence. Next, use the “Conditional Formatting” tool to highlight the missing numbers in a different color. This will allow you to easily identify and locate the missing numbers in the sequence. Additionally, you can use the “COUNT” or “COUNTA” functions to determine the total number of numbers in the sequence and compare it to the actual number of numbers present. This will help you identify any gaps or discrepancies in the sequence. By following these steps, you can efficiently identify missing numbers in a sequence using Excel.
Excel: Identify Missing Numbers in a Sequence
You can use the following formula in Excel to identify missing numbers in a sequence:
=FILTER(NOT(COUNTIF(A2:A13,SEQUENCE(D2+1-D1,,D1)))*SEQUENCE(D2+1-D1,,D1),NOT(COUNTIF(A2:A13,SEQUENCE(D2+1-D1,,D1))))
This particular formula finds all of the missing values in the sequence in the range A2:A13.
This formula assumes that cell D1 contains the minimum value in the sequence and cell D2 contains the maximum value in the sequence.
The following example shows how to use this formula in practice.
Example: Identify Missing Numbers in a Sequence in Excel
Suppose we have the following column of numbers in Excel:
Notice that the values range from 1 to 19.
Suppose we would like to identify all of the values that are missing in this sequence.
First, we’ll type the following formulas into cells D1 and D2 to find the minimum and maximum values in the sequence:
- D1: =MIN(A2:A13)
- D2: =MAX(A2:A13)
The following screenshot shows how to use these formulas in practice:
Next, we can type the following formula into cell C5 to find all missing values in the sequence:
=SMALL(IF(ISNA(MATCH(ROW(A$1:A$13),A$1:A$13,0)),ROW(A$1:A$13)),ROW(A1))
The following screenshot shows how to use this formula in practice:
Column C displays every missing value from the sequence of numbers in column A.
For example, we can see that the values 2, 3, 7, 10, 11, 15 and 18 are all missing from column A.
Note that this formula will work with any sequence of numbers.
For example, we can use the same formula to identify the missing values in the following sequence that ranges from 10015 to 10031:
The formula correctly identifies the missing values in the sequence.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel: