Find Missing Numbers In A Series In Excel

This post demonstrates how to find the missing piece from a given series in excel.It could be used to validate the data with a predefined series.

Let’s see how to find the missing values in a series in excel.

We will see a formula that we could use to find the missing values in a series whose starting value is known to us.




The series below starts with 20.

Capture

Step 1

Enter the formula below in cell C1

=IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDIRECT(“20:”&MAX($A$1:$A$9))),$A$1:$A$9,0)),ROW(INDIRECT(“20:”&MAX($A$1:$A$9)))),ROWS(D$1:D1)),””)

Notice that I have hard coded the formula with 20 in the Indirect function, which is our starting number in the series. Change 20 to the number your series starts with.

You don’t have to worry about the maximum number at which the series ends because the MAX function will automatically take care of that.

Also it’s an array formula so press Ctrl+Shift+Enter after entering the formula.




Capture

Step 2

Copy the formula in cell C1 to the entire column to see the missing values in the series.

Capture

As only 23 and 25 are missing from our series, they are displayed.

Hope this helped.




Share The Knowledge

Random Posts

Leave a Reply