Prev: Combination of IF, AND, and COUNTIF; need help
Next: SD03*10 -how do I just get the numbers at the end in cell after *
From: Iriemon on 12 May 2010 15:28
I have a series of numbers in one column (sample):

1
2
4
5
7
16


What formula would find the smallest available number? In other words how
would I find "3"?


From: Bernd P on 12 May 2010 15:44
Hello,

Array-enter
=INDEX(ROW(INDIRECT(MIN(A1:A6)&":"&MAX(A1:A6))),MATCH(TRUE,ISNA(MATCH(ROW(INDIRECT(MIN(A1:A6)&":"&MAX(A1:A6))),A1:A6,0)),
0))

Regards,
Bernd
From: T. Valko on 12 May 2010 16:11
Assuming the range of numbers is 1 to 16.

Array entered** :

=SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:16")),A2:A7,0)),ROW(INDIRECT("1:16"))),1)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Iriemon" <Iriemon(a)discussions.microsoft.com> wrote in message
news:DDAA5B97-47B4-4875-83E3-D9882A01FE63(a)microsoft.com...
>I have a series of numbers in one column (sample):
>
> 1
> 2
> 4
> 5
> 7
> 16
>
>
> What formula would find the smallest available number? In other words how
> would I find "3"?
>
>


 | 
Pages: 1
Prev: Combination of IF, AND, and COUNTIF; need help
Next: SD03*10 -how do I just get the numbers at the end in cell after *