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
- Click in formula bar
- Paste array formula in formula bar
- Press and hold Ctrl + Shift
- Press Enter
What is named ranges?
Download excel example file.
Missing numbers in a range.xls
(Excel 97-2003 Workbook *.xls)
Functions in this article:
ROW(reference) Returns the rownumber of a reference
Counts the number of cells within a range that meet the given condition
SMALL(array,k) Returns the k-th smallest row number in this data set.
ROWS(array) returns the number of rows in a reference or an array
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE