Looking for something similiar to MIN

I am looking for a MIN function that will tell me the smallest number in a
column of numbers, but only if it is unique. For example, if the column has 2
instances of the number 5, and it is the smallest, then I don't care, but if
there is only one instance, then I want to know about it. Ideally, it would
return the cell that it is in, rather than just the number.

Anybody know of something like that?

Thanks,
Steve

Steve Haack wrote:
> I am looking for a MIN function that will tell me the smallest number in a
> column of numbers, but only if it is unique. For example, if the column has 2
> instances of the number 5, and it is the smallest, then I don't care, but if
> there is only one instance, then I want to know about it. Ideally, it would
> return the cell that it is in, rather than just the number.
>
> Anybody know of something like that?
>
> Thanks,
> Steve

=IF(COUNTIF(C1:C8,MIN(C1:C8))=1,"C"&MATCH(MIN(C1:C8),C1:C8,0),"no single")

Alan Beban

"Steve Haack" <[email protected]>ha scritto nel messaggio
[email protected]

> I am looking for a MIN function that will tell me the smallest number
> in a column of numbers, but only if it is unique. For example, if the
> column has 2 instances of the number 5, and it is the smallest, then
> I don't care, but if there is only one instance, then I want to know
> about it. Ideally, it would return the cell that it is in, rather
> than just the number.
>
> Anybody know of something like that?
>
> Thanks,
> Steve

If I have well understood you need a formula like this:

=IF(AND(A1=MIN(Your_Range),COUNTIF(Your_Range,A1)=1),A1,0)

Copy this formula in cell B1 (I suppose your data start at A1) and then copy
down till the lenght of your list.
Then you can use AutoFilter to search your value.

Hoping to be helpful...

Regards
--
Franz

----------------------------------------------------------------------------------------
To reply translate from italian InVento (no capital letters)
----------------------------------------------------------------------------------------

=MIN(IF(1/COUNTIF(A1:A10,A1:A10)=1,A1:A10))

entered with ctrl + shift & enter

if there can be blank cells within the range


=MIN(IF((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&"")=1,A1:A10))

--
Regards,

Peo Sjoblom


"Steve Haack" <[email protected]> wrote in message
news:[email protected]...
>I am looking for a MIN function that will tell me the smallest number in a
> column of numbers, but only if it is unique. For example, if the column
> has 2
> instances of the number 5, and it is the smallest, then I don't care, but
> if
> there is only one instance, then I want to know about it. Ideally, it
> would
> return the cell that it is in, rather than just the number.
>
> Anybody know of something like that?
>
> Thanks,
> Steve

=IF(FREQUENCY(A:A,(MIN(A:A)))=1,MATCH(MIN(A:A),A:A,FALSE))

HTH,
Bernd