How do I find a missing number in a sequence of numbers?

G

Guest

Looking for a function or formula that will identify a missing number in a
range of cells containing a sequence of numbers.

Can anyone help?
 
P

Peo Sjoblom

If it would be like 1,3,7,9,13,14,15,16,18,20 then you can create a list of
numbers 1 to 20 (assume they are in A1:A20), then adjacent to that list use
a formula like

=IF(ISNUMBER(MATCH(A1,MySequence,0)),"",A1)

where MySequence is the list you want to check, copy down to B20

--
Regards,

Peo Sjoblom

(No private emails please)
 
B

Biff

Hi!

Here's one way.....

Assume you have in A1:A9

1
2
3
4
5
7
8
9
10

6 is the missing value.

Array entered using the key combo of CTRL,SHIFT,ENTER:

=INDEX(ROW(INDIRECT(A1&":"&A9)),MATCH(0,COUNTIF(A1:A9,ROW(INDIRECT(A1&":"&A9))),0))

Biff
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Missing alphanumeric number in the sequence 1
setting up sequence sets with only 3 repeat once in excel 2
How to find min value in Excel text cells? 3
Excel VBA to move cursor from 1 cell to another, once original cell is filled with data. 3
Identify missing numbers in list 5
SUMPRODUCT formula I think w/ wild card & number vs number as text 8
min and max in a sequence 11
replace dash in a number sequence 1

Top