+ Reply to Thread
Results 1 to 3 of 3

Lookup values based on longitude and latitude

  1. #1
    Registered User
    Join Date
    10-24-2012
    Location
    Adelaide
    MS-Off Ver
    Excel 2019
    Posts
    79

    Lookup values based on longitude and latitude

    I'm attempting to set up a multi-conditional lookup (table 2) that can identify the value for which the adjacent lower and upper latitude and longitude (table 1) values fall between the input (C20 - F20) upper and lower latitude and longitude boundaries (table 2), OR, if there are multiple values with longitude and latitude coordinates that fall between the input boundaries to give the average of these values.

    The ultimate purpose of these calculations is to produce raster arrays (such as you would find in a geographical information system like ArcGIS) in excel using coordinates and values alone, structured like table 3.

    Note: for the purpose of simplicity I've just used simple values rather than longitude/latitude or eastings/northings
    Attached Files Attached Files
    Last edited by MattRNR; 01-05-2014 at 06:18 PM.

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

    Re: Lookup values based on longitude and latitude

    Raster Array sounds pretty awesome, but I have no clue what that is.

    Here's what I came up with:

    =SUMPRODUCT(($C$6:$C$15>=C20)*($D$6:$D$15<=D20)*($E$6:$E$15>=E20)*($F$6:$F$15<=F20)*(G6:G15))/SUMPRODUCT(($C$6:$C$15>=C20)*($D$6:$D$15<=D20)*($E$6:$E$15>=E20)*($F$6:$F$15<=F20))

    This is the average of column G, where the long and lats in the table are within the set parameters.


    Each criteria between the parentheses is checking against the parameters, and then summing all values in the last array of G where they are met. The second half of the formula simply identifies the number of matching rows without the final array, which is the denonimator of the average. Sum rows of G where parameters meet divided by the number of rows that met the parameters.
    Last edited by daffodil11; 01-02-2014 at 06:38 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
    10-24-2012
    Location
    Adelaide
    MS-Off Ver
    Excel 2019
    Posts
    79

    Re: Lookup values based on longitude and latitude

    Cheers mate, that worked perfectly, knew it'd be something simple like that but couldn't work it out for the life of me

    My apologies for the jargon: http://webhelp.esri.com/arcgisdeskto...raster_data%3F

    Have a good one

+ 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. Distance between latitude and longitude
    By fazna ali in forum Excel General
    Replies: 2
    Last Post: 11-06-2012, 05:03 AM
  2. Replies: 7
    Last Post: 05-02-2012, 01:00 AM
  3. Latitude/Longitude Porblem
    By edwardtong694 in forum Excel General
    Replies: 3
    Last Post: 05-20-2009, 12:23 PM
  4. [SOLVED] formula for longitude/latitude
    By Leo in forum Excel General
    Replies: 2
    Last Post: 06-03-2005, 02:05 PM
  5. Help with Latitude and Longitude.
    By LadiFireBug in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 01-22-2005, 05:09 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