smallest missing number

C

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
 
M

mphell0

If they will always be in ascending order and always only 6 numbers the
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 els
will come
along with a simpler formula
 
B

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

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
 
C

Chris_t_2k5

Thanks worked just how i wanted it to!

Bob Phillips said:
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)
Click to expand...
 
Top