Find Missing Number?

Ok probably a easy answer here, but I'm a excel Novice.

what I have is a series of numbers and what I want to find is what
Number are missing in between all the different numbers to complete a
whole set of number.

Example

I have: 1 2 6 7 9 13

How could I find whats missing as to make it straight count up from 1 to 13

And return from this example 3 4 5 8 10 11 12

Thanks for any help

Hi there,

one possibility is
in an additional column or sheet, i would create the whole series (type A1:
1, A2: = A1+1, then copy downwards until you have the required series)
then, next to this, so column B, IN b2:
=IF(ISNA(VLOOKUP(A2;THEOTHERSHEET_RANGE;1;0));"MISSING";"OK")

where: THEOTHERSHEET_RANGE is a range's 1st column is the original (and
gapped) series of numbers N.B. absolute cell reference with $-s, copy to
downwards with autofill and then you can see, which numbers are IN ("OK") and
which are missing /won't find out :-) / the "MISSING". then you can use the
DATA/ Autofilter/Set Autofilter and filter to MISSING

is this, what you are after?

Best regards,
ANdras
(Hungary)

"Djanvk" wrote:

> Ok probably a easy answer here, but I'm a excel Novice.
>
> what I have is a series of numbers and what I want to find is what
> Number are missing in between all the different numbers to complete a
> whole set of number.
>
> Example
>
> I have: 1 2 6 7 9 13
>
> How could I find whats missing as to make it straight count up from 1 to 13
>
> And return from this example 3 4 5 8 10 11 12
>
> Thanks for any help
>

Try something like this:

With your list of values in Cells A1:A10 (eg 1,2,6,7,9,13)

This ARRAY FORMULA lists the items in ascending order.
B1:
=INDEX(ROW($A$1:INDEX(A:A,MAX(A:A))),SMALL(IF(COUNTIF($A$1:INDEX(A:A,MAX(A:A)),ROW($A$1:INDEX(A:A,MAX(A:A)))+MIN(A:A)-1)=0,ROW($A$1:INDEX(A:A,MAX(A:A)))+MIN(A:A)-1),ROWS($1:1))-MIN(A:A))+MIN(A:A)

Note: To commit array formulas, hold down [Ctrl] and [Shift] when you press
[Enter].

Copy B1 and paste it into B2 and down as far as you need


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Djanvk" wrote:

> Ok probably a easy answer here, but I'm a excel Novice.
>
> what I have is a series of numbers and what I want to find is what
> Number are missing in between all the different numbers to complete a
> whole set of number.
>
> Example
>
> I have: 1 2 6 7 9 13
>
> How could I find whats missing as to make it straight count up from 1 to 13
>
> And return from this example 3 4 5 8 10 11 12
>
> Thanks for any help
>

Hey this worked good...

thanks, still going to try the other forumlas also

thanks

Ron Coderre wrote:
> Try something like this:
>
> With your list of values in Cells A1:A10 (eg 1,2,6,7,9,13)
>
> This ARRAY FORMULA lists the items in ascending order.
> B1:
> =INDEX(ROW($A$1:INDEX(A:A,MAX(A:A))),SMALL(IF(COUNTIF($A$1:INDEX(A:A,MAX(A:A)),ROW($A$1:INDEX(A:A,MAX(A:A)))+MIN(A:A)-1)=0,ROW($A$1:INDEX(A:A,MAX(A:A)))+MIN(A:A)-1),ROWS($1:1))-MIN(A:A))+MIN(A:A)
>
> Note: To commit array formulas, hold down [Ctrl] and [Shift] when you press
> [Enter].
>
> Copy B1 and paste it into B2 and down as far as you need
>
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "Djanvk" wrote:
>
>> Ok probably a easy answer here, but I'm a excel Novice.
>>
>> what I have is a series of numbers and what I want to find is what
>> Number are missing in between all the different numbers to complete a
>> whole set of number.
>>
>> Example
>>
>> I have: 1 2 6 7 9 13
>>
>> How could I find whats missing as to make it straight count up from 1 to 13
>>
>> And return from this example 3 4 5 8 10 11 12
>>
>> Thanks for any help
>>