Identify missing numbers in a range
Question: How do I find missing numbers between 1-9 in a range?
1 3 4
5 6 7
8 8 3
Array formula in cell B8:
If you have numbers between 5 and 32, change ROW($1:$9) to ROW($5:$32)
The formula won´t work if you have numbers above 1048576 (excel 2007). See this post: Identify missing values in a column using excel formula
How to create an array formula
- Copy above array formula
- Select cell B8
- Press with left mouse button on in formula bar
- Paste array formula in formula bar
- Press and hold Ctrl + Shift
- Press Enter
Explaining formula in cell B8
Step 1 - Check if numbers in range equal numbers in list
The COUNTIF function counts values based on a condition, in this case, multiple conditions. The first argument is the cell range containing the numbers, the second argument is a given list of numbers.
The ROW function returns the row number of a cell, in this case a cell range. The ROW function then returns an array of row numbers of all cells in the cell range.
COUNTIF($B$3:$D$5, ROW($1:$9)
becomes
COUNTIF({1,3,4; 5,6,7; 8,8,3},{1; 2; 3; 4; 5; 6; 7; 8; 9})
and returns
{1;0;2;1;1;1;1;2;0}.
Step 2 - Which numbers are missing?
A 0 (zero) indicates a number is missing, compare the value with 0 (zero) and we get either TRUE or FALSE.
COUNTIF($B$3:$D$5,ROW($1:$9))=0
becomes
{1;0;2;1;1;1;1;2;0}=0
and returns
{FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE}.
Step 3 - Replace TRUE with corresponding number
IF(COUNTIF($B$3:$D$5, ROW($1:$9))=0, ROW($1:$9), "")
becomes
IF({FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE}, ROW($1:$9), "")
becomes
IF({FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE}, {1; 2; 3; 4; 5; 6; 7; 8; 9}, "")
and returns
{"";2;"";"";"";"";"";"";9}.
Step 4 - Extract a new number in a cell each
The SMALL function extracts the k-th smallest number from a cell range or array. SMALL( cell_ref, k)
The second argument contains the ROWS function and an expanding cell range. When you copy the cell and paste to cells below the cell reference grows. This makes sure that a new numb er is extracted in each cell.
SMALL(IF(COUNTIF($B$3:$D$5, ROW($1:$9))=0, ROW($1:$9), ""), ROWS(B8:$B$8)
becomes
SMALL({"";2;"";"";"";"";"";"";9}, ROWS(B8:$B$8)
becomes
SMALL({"";2;"";"";"";"";"";"";9}, 1)
and returns 2 in cell B8.
Get Excel *.xls file
Missing numbers in a range.xls
Missing values category
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
This article shows how to compare two nonadjacent cell ranges and extract values that exist only in one of the […]
Question: I want to find missing numbers in two ranges combined? They are not adjacent. Answer: Array formula in cell […]
Excel categories
5 Responses to “Identify missing numbers in a range”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
I have a worksheet that has rows, each containing sequential groupings of values of "1" and "0" These alternate across 1800 colunms of data.
My question: how do I count the number of groupings of each? In other words, across those 1800 columns, how many arrays of value 1 and how many arrays of value 0 do I have?
Thanks.
Joe
Joe,
see this post:
Count the number of groupings of each value
thanks
Is there any difference to apply this formula in only one column (F2:F1093)? I´m trying to identify missing numbers in a list but I´m making something wrong.
Felipe,
Did you enter it as an array formula?