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:C 8),C1:C8,0),"no single")

Alan Beban

"Steve Haack" ha scritto nel messaggio


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" wrote in message
...
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