+ Reply to Thread
Results 1 to 8 of 8

Need ROW number based on a MIN array formula.

  1. #1
    Registered User
    Join Date
    08-18-2013
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    4

    Exclamation Need ROW number based on a MIN array formula.

    Need to figure out how to get the following:

    I have a large set of LAT-A and Long-A coords and a separate file of LAT-B and LONG-B coords (50,000 rows or more each)
    I have been able to find the distance to the CLOSEST point in the LAT-A/LONG-A points to a single LAT-B and LONG-B. But I can NOT figure out how to tell me the ROW in the LAT-A and Long-A worksheet was the CLOSEST.

    Sheet with LAT-A and LONG-A
    LAT-A LONG-A
    1 23.81142998 91.26706696
    2 26.22337 78.173622
    3 23.81106758 91.26634979
    4 23.81109238 91.26612854
    etc

    Sheet with LAT-B and LONG-B
    LAT-B LONG-B
    1 19.18746948 72.8460
    2 26.20013618 78.1618
    3 26.19869995 78.1663
    4 23.81142998 91.2671
    etc

    Here is my formula to determine the closest LAT-A/LONG-A point to LAT-B / LONG-B
    {=MIN(0.621371*(3958.818*((2*ASIN(SQRT((SIN((RADIANS(LATB)-RADIANS($LATA$2:LATA$50000))/2)^2)+COS(RADIANS(LATB))*COS(RADIANS(LATA$50000))*(SIN((RADIANS(LONGB)-RADIANS(LONGA$2:LONGA$50000))/2)^2)))))))}

    It works great.

    What I am trying to do is figure out WHICH ROW from the LAT-A/LONG-A tab provided the shortest distance!

    So basically, a long formula determines the shortest distance between one point and thousands of others...but I can't figure out how to know WHICH of those thousands gave me the closest point.

    Any help would be much appreciated.

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,450

    Re: Need ROW number based on a MIN array formula.

    I don't really understand what you need but this is how I would approach this.

    use
    Please Login or Register  to view this content.
    to find the first row that is larger than the value you are looking for.

    calculate whether this row is closer to your value or the previous row.

    present the closest value as your answer.

    You should have posted an example.

  3. #3
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Need ROW number based on a MIN array formula.

    You are combinig data from two columns to get the MIN.
    For which value do you need the ROW number to be returned?

  4. #4
    Registered User
    Join Date
    08-18-2013
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Need ROW number based on a MIN array formula.

    Let me try to explain a difference way...

    I have attacehd a file.
    Basically trying to find the row number but way more complicated than ROW() or match() (I think). Maybe I am missing something.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-18-2013
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Need ROW number based on a MIN array formula.

    Hopefully my new post helps answer your question... Sorry for not including the file before.

  6. #6
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Need ROW number based on a MIN array formula.

    =MATCH(MIN(ABS($A$2:$A$5-$D2)+ABS($B$2:$B$5-$E2)),ABS($A$2:$A$5-$D2)+ABS($B$2:$B$5-$E2),0)
    Confirm Control+shift+Enter

  7. #7
    Registered User
    Join Date
    08-18-2013
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Need ROW number based on a MIN array formula.

    Absolutely awesome solution. Thanks!

  8. #8
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Need ROW number based on a MIN array formula.

    You are welcome.

+ 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. Replies: 1
    Last Post: 07-18-2013, 10:11 AM
  2. [SOLVED] Array based on a formula
    By gvaltat in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-11-2013, 04:03 PM
  3. Replies: 0
    Last Post: 01-11-2012, 09:55 AM
  4. [SOLVED] Resize array based on number of 'used' elements
    By John in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-22-2006, 02:30 AM
  5. [SOLVED] Serial number allocation - Array formula
    By Space Ape in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 06:05 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