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?


--
mphell0
------------------------------------------------------------------------
mphell0's Profile: http://www.excelforum.com/member.php...o&userid=30153
View this thread: http://www.excelforum.com/showthread...hreadid=513202

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

=IF(G1-F11,F1+1,IF(H1-G11,G1+1,IF(I1-H11,H1+1,IF(J1-I11,I1+1,IF(K1-J11,J1+1,K1+1)))))

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


--
mphell0
------------------------------------------------------------------------
mphell0's Profile: http://www.excelforum.com/member.php...o&userid=30153
View this thread: http://www.excelforum.com/showthread...hreadid=513202

Try this

=SMALL(IF(ISERROR(MATCH(ROW(INDIRECT("1:15")),F1:K 1,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" wrote in message
...
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

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:K 1,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

Thanks worked just how i wanted it to!

"Bob Phillips" wrote:

Try this

=SMALL(IF(ISERROR(MATCH(ROW(INDIRECT("1:15")),F1:K 1,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" wrote in message
...
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.


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=513202