Results 1 to 12 of 12

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

Threaded View

SolarDesign Finding closest location to a... 11-09-2021, 08:58 PM
bsalv Re: Finding closest location... 11-09-2021, 10:56 PM
FlameRetired Re: Finding closest location... 11-10-2021, 05:35 PM
GeoffW283 Re: Finding closest location... 11-09-2021, 11:09 PM
SolarDesign Re: Finding closest location... 11-10-2021, 04:20 PM
bsalv Re: Finding closest location... 11-10-2021, 04:32 PM
SolarDesign Re: Finding closest location... 11-10-2021, 05:18 PM
bsalv Re: Finding closest location... 11-10-2021, 06:48 PM
bsalv Re: Finding closest location... 11-10-2021, 06:52 PM
SolarDesign Re: Finding closest location... 11-10-2021, 07:30 PM
GeoffW283 Re: Finding closest location... 11-11-2021, 01:26 AM
bsalv Re: Finding closest location... 11-11-2021, 02:39 AM
  1. #1
    Registered User
    Join Date
    11-04-2021
    Location
    USA
    MS-Off Ver
    365
    Posts
    13

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

    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.

    =INDEX($D$4:$D$1890,MATCH(MIN(SQRT(($E$4:$E$1890-R4)^2+($G$4:$G$1890-S4)^2)),SQRT(
    ($E$4:$E$1890-R4)^2+($G$4:$G$1890-S4)^2),0),1)
    *Note: the zip code data was removed and just shown as the locations on the few station locations included so the formulas evaluate correctly.
    Attached Files Attached Files
    Last edited by SolarDesign; 11-10-2021 at 07:32 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 11
    Last Post: 06-05-2023, 01:36 AM
  2. X and Y coordinates, locating closest Point to point distance.
    By Erav in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-13-2020, 04:02 PM
  3. [SOLVED] Finding Closest Point and Distance
    By batman07 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-25-2017, 08:06 AM
  4. [SOLVED] Retrieving the Closest Larger / Closest Smaller Values from a List
    By ChipsSlave in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-31-2017, 08:38 AM
  5. Geofencing Lat / Longs (Finding Closest Point) in Excel
    By mattkunkle in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-19-2013, 02:05 PM
  6. [SOLVED] get 10 Closest Larger / Closest Smaller Values from a List
    By Auni in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-19-2013, 04:07 AM
  7. Replies: 0
    Last Post: 08-25-2005, 02:37 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1