+ Reply to Thread
Results 1 to 4 of 4

Using map co-ordinates and map regions

  1. #1
    Registered User
    Join Date
    03-18-2011
    Location
    Ireland
    MS-Off Ver
    Excel 2007
    Posts
    2

    Using map co-ordinates and map regions

    I have a problem that I hope someone can help me with.

    I have a map with a standard numerical x,y co-ordinate system. Within the map I have regions that are all the same size e.g. co-ordinates 0,0 to 19,19 are Region A; 20,20 to 39,39 are Region B going up to 99,99 in Region E then continuing in the next 'row' of regions such that co-ordinates 0,20 are in Region F; 24,49 are in Region L etc.

    I want excel to return the region when a set of co-ordinates are entered. Now for a small map with only a few regions I can use the IF and AND/OR functions.

    However my map is now 500 x 500 squares and the regions are 50 x 50 squares (i.e. 100 regions going from Region 1 to 100 in a 10 x 10 array). I can't figure out if there is a relatively simple formula to achieve this.

    Any suggestions?

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Using map co-ordinates and map regions

    Welcome to the forum.

    Assuming the map regions can be represented as a 2D table with x coordinates ascending across the top row, y coordinates ascending down the left column, and region name in the body, then a simple 2D lookup will return the region from the coordinates.

    See http://www.ozgrid.com/forum/showthre...t=22982&page=1 or Google excel 2d lookup for more results.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    03-18-2011
    Location
    Ireland
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Using map co-ordinates and map regions

    Thanks for the welcome and reply. When the map was smaller I had been working with the INDEX and MATCH functions. As the map grows and the region size changes it means having to regenerate the 2D table which is manually time consuming. My question related to one of 2 potential solution:
    First was whether I needed a 2D 'lookup' table at all or whether there was a way of writing a formula that would not rely on a 2D table.
    Second was if indeed I do need a 2D table was whether I could use VBA to repopulate that table with data when the map and region sizes change.

    However for now I can use the 2D table method. The map will not change appreciably for a few weeks so I'll keep trying.

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Using map co-ordinates and map regions

    First was whether I needed a 2D 'lookup' table at all or whether there was a way of writing a formula that would not rely on a 2D table.
    Not knowing how your data is arranged, probably. The 2D lookup would certainly be faster.

    whether I could use VBA to repopulate that table with data when the map and region sizes change.
    Again, not knowing the nature of your data, probably.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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