smallest missing number

[Chris_t_2k5]

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

[mphell0]

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

[mphell0]

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.

[Bob Phillips]

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

[CLR]

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

[Chris_t_2k5]

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
>
>
>

[Cutter]

Sounds like you're playing pool.