+ Reply to Thread
Results 1 to 8 of 8

Minimum result of complex array function

  1. #1
    Registered User
    Join Date
    08-07-2012
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Minimum result of complex array function

    I have a list of all ZIP Codes in the US and their approximate latitude and longitude. I have another list of select airports and their ZIP codes. I have a third list of shipments and their originating ZIP Codes. What I'm trying to determine is the closest airport to each shipment.

    I'm using the following formula to determine the distance in miles.

    Please Login or Register  to view this content.
    I've adapted it into an array formula thus:

    Please Login or Register  to view this content.
    Evidently, though, the formula isn't evaluating the way I was expecting. It doesn't even seem to evaluate each member of tblTerms[ZIP] separately.

    There's probably a better approach to this entirely, but I sure don't know what it is. Any advice?

    I've attached a simplified sample file (missing about half the ZIPs to get under the size limit, but should still work).
    Attached Files Attached Files

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Minimum result of complex array function

    I frequently use the HaverSine formula in your post for distance calculation, so I can attest that it definitely works. I'm having some trouble understanding point to and from you are calculating.

    One point appears to be the Zip from A to the matched lat and long in the Zips table. What is the other point?\\

    Are you trying to find the shortest distance from A to any point in Terminals? This will always be zero, since the zip code you're looking up is in the list of other points.

    Please Login or Register  to view this content.
    If you only need the shortest distance, than you don't need to know which Terminal it's coming from. If you only need to know the closest terminal, then you don't need to show the shortest distance.
    Last edited by daffodil11; 09-14-2015 at 03:39 PM.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    08-07-2012
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Minimum result of complex array function

    Yes, I'm looking for the shortest distance (i.e., nearest terminal) from point A to any point in tblTerms. Point A isn't usually a terminal, though, so it wouldn't be likely to appear in tblTerms.

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Minimum result of complex array function

    Aha, I understand now.

    We can use a slick trick I learned over HERE to bend this formula to our will and return multiple INDEX's in an array.

    Please Login or Register  to view this content.
    Edit: Rewritten for table references
    Last edited by daffodil11; 09-14-2015 at 04:38 PM.

  5. #5
    Registered User
    Join Date
    08-07-2012
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Minimum result of complex array function

    Very nice! That seems to have done the job. I'm going to have to give that link a thorough read through. Thanks!

  6. #6
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Minimum result of complex array function

    No problem, glad I could help out. Here's the finished version for future reference.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    08-07-2012
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Minimum result of complex array function

    As a side note, how would I determine which terminal it was that was closest?

  8. #8
    Registered User
    Join Date
    08-07-2012
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Minimum result of complex array function

    Oh, you did it already in the file. Nice. Thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Assistance with Minimum Length array function
    By arkan01d in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-13-2014, 09:09 PM
  2. [SOLVED] Using Offset function as the array in the PercentRank function is giving wrong result
    By Bobneil in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-06-2013, 09:29 PM
  3. Replies: 1
    Last Post: 01-25-2013, 01:58 PM
  4. Complex array lookup function?
    By hodeware in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-13-2011, 03:41 AM
  5. Array function for summing the minimum between two columns
    By shwayze24 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-03-2011, 12:24 PM
  6. [SOLVED] complex excel formula Array how do I convert it to a vba Function
    By Rob in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-10-2006, 02:10 PM
  7. [SOLVED] Complex function....not sure how if the result I want is possible
    By Besseme in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-28-2006, 12:00 AM

Tags for this Thread

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