Problem with SMALL function and multiple same values

[Ryan]

I have a column with about 1000 distances in miles to a point and I am
trying to use the SMALL function to give me the closest, 2nd closest,
3rd closest, etc. The problem is some of the distances are exactly
the same. For example the SMALL function is returning the same
distance for the 2nd and 3rd distance which it should. However, when
using the CELL("Address") function to give me the exact address of the
cell that the SMALL function is returning I get the same cell address
for the 2nd and 3rd distances.

Example:
4.4
5.7
9.9
4.4
3.0
11.1
15.1

SMALL(A1:A7,1) = 3.0; then using the Cell(Address) function with Small
nested to return $A$5

the problem arises with the 2nd and 3rd
SMALL(A1:A7,2) = 4.4; then using the Cell(Address) function with Small
nested to return $A$1
SMALL(A1:A7,3) = 4.4; then using the Cell(Address) function with Small
nested to return $A$1....I was hoping that this would return$A$4

Can anyone suggest a fix for how I can get this to work? Thanks!

 

[T. Valko]

One way...

Use a helper column that will give each distance a *unique* value then you
can look for the smallest n value.

A2:A10 = distances

Enter this formula in B2 and copy down to B10:

=A2+ROW()/10^10

Then to get the cell address for the n smallest distance:

=ADDRESS(MATCH(SMALL(B$2:B$8,n),B$2:B$8,0)+ROW(B$2)-1,1,4)

Where n = nth smallest

 

[Ryan]

THANKS! This is a very elegant solution, I justed added ROW()/10^10
to the distance calculation field and it works. Much simpler than the
solution I was working on from Microsoft's site:

http://office.microsoft.com/en-us/excel/HA012260381033.aspx

Best,
Ryan

 

[T. Valko]

You're welcome. Thanks for the feedback!

One thing to consider, the way you did it, if you need to do other calcs on
those distances you'll have to remember that the numbers you see as 4.4 may
actually be 4.4000000002.

 

[Bernd P]

Hello Biff,

Your approach can still lead to dupes, I think (ok, not if distances
are only given with less than 10 decimals).

I would suggest to use my countif + countif approach as a substitute
for small:
http://www.sulprobil.com/html/sorting.html

Regards,
Bernd

 

[T. Valko]

Your approach can still lead to dupes, I think (ok, not if distances

are only given with less than 10 decimals).

That's really stretching things!

I considered using the RANK(...)+COUNTIF(...)-1 method but the OP said they
have ~1000 rows of data.

Using an ever-increasing range reference like A$1:A1, by the time you get to
row 1000, A$1:A1000, you've referenced this many cells:

=SUMPRODUCT(ROW(1:1000))

 

[Bernd P]

Hello Biff,

You are right with your count of references, I think. But the
comparison of the runtime of our approaches depends on how many calls
to SMALL you have to add to your (admittedly, quick) formula, I guess.
For 1,000 cells your formula would only need 1 msec (on my 2 GHz dual
core) but 1,000 calls to SMALL (or one corresponding array formula)
would need 350 msec. My formula would need 220 msec, plus 1,000 INDEX-
MATCH calls would add 25 msec. BTW: Your RANK-COUNTIF approach would
need only 120 msec...

If the number of SMALL calls is low and if distances are given with
less than 7 decimals (10 was wrong) then your approach seems to be
preferrable.

Regards,
Bernd