Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I find a missing number in a sequence of numbers?
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? |
#2
|
|||
|
|||
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) "Nash" wrote in message ... 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? |
#3
|
|||
|
|||
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 "Nash" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
Automatic Number Sequence | Excel Discussion (Misc queries) | |||
The formula to find the smallest number in a row not equal to zero | Excel Worksheet Functions | |||
Using the Find tool in EXCEL | Excel Worksheet Functions | |||
How do I find the first value in a column less than a number? | Excel Worksheet Functions |