software

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: www.excelforum.com/member.php...oamp;userid=30153
View this thread: 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-F1gt;1,F1 1,IF(H1-G1gt;1,G1 1,IF(I1-H1gt;1,H1 1,IF(J1-I1gt;1,I1 1,IF(K1-J1gt;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.--
mphell0
------------------------------------------------------------------------
mphell0's Profile: www.excelforum.com/member.php...oamp;userid=30153
View this thread: www.excelforum.com/showthread...hreadid=513202Try this

=SMALL(IF(ISERROR(MATCH(ROW(INDIRECT(quot;1:15quot;)),F1:K 1,0)),ROW(INDIRECT(quot;1:15quot;)
)),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)

quot;Chris_t_2k5quot; gt; wrote in message
...
gt; Here is my problem,
gt;
gt; I have numbers in cells F1:K1 eg, 1,2,3,5,6,7.
gt;
gt; Is it possible to have in cell L1 a function / formula to display the
gt; smallest missing number (ie. 4 in the example). The missing number can be
gt; anything upto 15.
gt;
gt; 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)gt;SMALL(F1:K1,1) 1,SMALL(F1:K1,1) 1,IF(SMALL(F1:K1,3)gt;SMALL(F1:K1,2) 1,SMALL(F1:K1, 2) 1,IF(SMALL(F1:K1,4)gt;SMALL(F1:K1,3) 1,SMALL(F1:K 1,3) 1,IF(SMALL(F1:K1,5)gt;SMALL(F1:K1,4) 1,SMALL(F1 :K1,4) 1,IF(SMALL(F1:K1,6)gt;SMALL(F1:K1,5) 1,SMALL( F1:K1,5) 1,quot;yukquot;)))))

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

Vaya con Dios,
Chuck, CABGx3
quot;Chris_t_2k5quot; wrote:

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

Thanks worked just how i wanted it to!

quot;Bob Phillipsquot; wrote:

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


Sounds like you're playing pool.--
Cutter
------------------------------------------------------------------------
Cutter's Profile: www.excelforum.com/member.php...foamp;userid=9848
View this thread: www.excelforum.com/showthread...hreadid=513202