smallest missing number

Here is my problem,

I have numbers in cells F1:K1 eg, 1,2,3,5,6,7.

Is it possible to have in cell L1 a function / formula to display the
smallest missing number (ie. 4 in the example). The missing number can be
anything upto 15.

Thanks

Will the number always be in ascending order from left to right?

If they will always be in ascending order and always only 6 numbers then the
following formula will work:

=IF(G1-F1>1,F1+1,IF(H1-G1>1,G1+1,IF(I1-H1>1,H1+1,IF(J1-I1>1,I1+1,IF(K1-J1>1,J1+1,K1+1)))))

Its not very elegant but it gets the job done. Perhaps someone else will come
along with a simpler formula.

Try this

=SMALL(IF(ISERROR(MATCH(ROW(INDIRECT("1:15")),F1:K1,0)),ROW(INDIRECT("1:15")
)),1)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Chris_t_2k5" <[email protected]> wrote in message
news:[email protected]...
> Here is my problem,
>
> I have numbers in cells F1:K1 eg, 1,2,3,5,6,7.
>
> Is it possible to have in cell L1 a function / formula to display the
> smallest missing number (ie. 4 in the example). The missing number can be
> anything upto 15.
>
> Thanks

Thanks worked just how i wanted it to!

"Bob Phillips" wrote:

> Try this
>
> =SMALL(IF(ISERROR(MATCH(ROW(INDIRECT("1:15")),F1:K1,0)),ROW(INDIRECT("1:15")
> )),1)
>
> which is an array formula, it should be committed with Ctrl-Shift-Enter, not
> just Enter.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Chris_t_2k5" <[email protected]> wrote in message
> news:[email protected]...
> > Here is my problem,
> >
> > I have numbers in cells F1:K1 eg, 1,2,3,5,6,7.
> >
> > Is it possible to have in cell L1 a function / formula to display the
> > smallest missing number (ie. 4 in the example). The missing number can be
> > anything upto 15.
> >
> > Thanks
>
>
>

Sounds like you're playing pool.

Here's something.........it doesn't deal well with zero or decimals, but
seems to work with whole numbers pretty good...........

=IF(SMALL(F1:K1,2)>SMALL(F1:K1,1)+1,SMALL(F1:K1,1)+1,IF(SMALL(F1:K1,3)>SMALL(F1:K1,2)+1,SMALL(F1:K1,2)+1,IF(SMALL(F1:K1,4)>SMALL(F1:K1,3)+1,SMALL(F1:K1,3)+1,IF(SMALL(F1:K1,5)>SMALL(F1:K1,4)+1,SMALL(F1:K1,4)+1,IF(SMALL(F1:K1,6)>SMALL(F1:K1,5)+1,SMALL(F1:K1,5)+1,"yuk")))))

Remember, the formula goes all on one line, watch out for email word-wrap.

Vaya con Dios,
Chuck, CABGx3



"Chris_t_2k5" wrote:

> Here is my problem,
>
> I have numbers in cells F1:K1 eg, 1,2,3,5,6,7.
>
> Is it possible to have in cell L1 a function / formula to display the
> smallest missing number (ie. 4 in the example). The missing number can be
> anything upto 15.
>
> Thanks