=SMALL function not working due to "ties"

[ARehn]

I am working on a tool to determine the 5 closest locations to a zip code. The user will enter a zip code on the "Main Page" in C5 and the 5 closest locations on page 2 will populate in column F 4:8. I am using the following to determine the distance between zip codes:

=6371*2*ASIN(SQRT(SIN((RADIANS('Main Page'!$C$8)-RADIANS(Table2[@Column4]))/2)^2+COS(RADIANS('Main Page'!$C$8))*COS(RADIANS(Table2[@Column4]))*SIN((RADIANS(Table2[@Column3])-RADIANS('Main Page'!$C$7))/2)^2))

I then use math to convert to miles, because, well, the USA. Using

=SMALL(Sheet2!P:P,{1;2;3;4;5})

to get my five closest locations works great, most of the time. It is when I have a zip code with multiple locations that the results are skewed. Due the "tie" I get results that are duplicated and others are omitted. What can I use in place of =SMALL to give me the results when a specific zip code has "ties"

The other problem I am running into is that C5 on the main page is a Geography Data Type. Excel does not like East coast zip codes that begin with 0. If I use a zip code for Trenton, NJ, 08601, it does not return a result because the beginning 0 gets dropped. If I use '08601, then the Geography Data Type gets erased. Any ways around this?

 

[KRice]

I’m not quite following the problem. It sounds as if every “location” that shares a common ZIP code uses identical latitude and longitude coordinates? Are you using a central single-point location to represent all addresses or locations within a ZIP code? If you want the five smallest distances based on the lat-long coordinates for each location, then you’ll need to use the lat-long coordinates for the locations, not a much-less granular lat-long applied to all locations within a ZIP code. On the other hand, if you want to use a less granular single lat-long for all locations within a ZIP code, then it would make sense to filter the list of ZIPs to ensure that only unique ZIP codes are considered…and there would be no duplicates.

Would you clarify these points further? I don’t know if you’ve seen a similar thread where the smallest distances were sought based on address-specific lat-longs:

Calculating shortest to longest distances between Zip Codes (using latitude and longitude)

Hi, I have what I believe might be an interesting one I could use some help on. I've have been working on formulas to show the distances (Miles) between multiple longitude and latitudes (Zip Codes). I am trying to find the shortest distance between multiple Zip Codes. Then put in order the...

www.mrexcel.com

It is conceivable that two locations might produce the same distances, perhaps even locations in different ZIP codes or different states. I’m assuming that in the case of those “ties”, you would still want them reported back in the “shortest five” list?