Finding closest location to a point, as well as the next two closest.

[SolarDesign]

Hello everyone, I've got a data set of temperature recording stations, their lat / lon, and a list of zip codes with their corresponding geographic center lat / lon. I've got a function that will find the closest station for a given zip code, but for a number of reasons the closest may not be the best choice, so I would like to also return the second and third closest stations as well. I don't intend for this data to really be interacted with by anyone using the full workbook so I don't want to set it up as a pivot and make everyone manually transcribe information over.
I've got a nice function that when I give it lat / lon values from my zip code list will search through the list of stations and show the nearest, now I'd just like to somehow show the next two closest.

Please Login or Register  to view this content.

*Note: the zip code data was removed and just shown as the locations on the few station locations included so the formulas evaluate correctly.

[bsalv]

see attachment (same formula)

[GeoffW283]

Changes to your existing formula are highlighted in red:

Change the highlighted 2 to 3 to get the 3rd closest

It's the same formula change essentially as Bsalv, except I leave the layout to you.

Please Login or Register  to view this content.

[SolarDesign]

Thank you for the responses, now I'm just working out how to calculate the distance between the lat/lon points to show how far the stations are from the given point.

[bsalv]

distance = my column Y ?

[SolarDesign]

distance = my column Y ?

When I adapted your setup to my full set the distances in that column weren't accurate, so I tried tweaking that, and also using

Please Login or Register  to view this content.

(I tried as both the converted radian values, and the decimal values already present, which either showed thousands of miles of difference, or about a mile apart...)
From an Excel tips post that this forum won't allow me to link to that can be found searching for "Calculating the Distance between Points"

No matter how I set it up the distances are always way off for some reason.

Here's 3 stations with their coordinates and known distances using what you put together on the previous worksheet plugged into the table.

[FlameRetired]

see attachment (same formula)

Sorry for off-topic interjection:

Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

[bsalv]

link https://www.contextures.com/excellat...tude.html#code
an UDF to calculate the distance.
the lat and lon in the table are the columns "Lat" and "Lon x(-1)", my error.
When using this UDF the distances are almost correct.

[bsalv]

i couldn't add the attachment in the previous reaction (?)

[SolarDesign]

Worked out great! Thanks!

[GeoffW283]

In post #6 you were attempting a distance formula that looks a little like the "Haversine" formula, but without luck.

In case you are still interested in a formula based approach, I added a Haversine implementation to bsalv's post #9 in workbook cell Q4:

Please Login or Register  to view this content.

Here is how it compares with bsalv's Vincenti implementation:

distance.png

The Haversine model, per Wikipedia, can have errors of up to 0.5% as it approximates the earth's shape to a sphere, but maybe that is good enough for your purposes?

On the other hand, the Vincenti approach is millimeter-accurate relative to the standard WGS-84 oblate spheroid model

Attached is bsalv's post #9 workbook with the Haversine formula in Col-Q.

Oh, and thanks for the rep!

[bsalv]

the method used in the UDF is https://en.wikipedia.org/wiki/Vincenty%27s_formulae

The first (direct) method computes the location of a point that is a given distance and azimuth (direction) from another point. The second (inverse) method computes the geographical distance and azimuth between two given points. They have been widely used in geodesy because they are accurate to within 0.5 mm (0.020 in) on the Earth ellipsoid.

So I also think the small formula from GeoffW283 is good enough to make your ranking, you don't need the exact distance with high precision.