Looking for something similiar to MIN

S

Steve Haack

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
 
A

Alan Beban

Steve said:
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
Click to expand...

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

Alan Beban
 
F

Franz

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
Click to expand...

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
 
P

Peo Sjoblom

=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))
 
Top