+ Reply to Thread
Results 1 to 10 of 10

nearest value

  1. #1
    Forum Contributor
    Join Date
    04-29-2012
    Location
    nuneaton, England
    MS-Off Ver
    Excel 2007
    Posts
    366

    nearest value

    i have heard about index, match and vlookups,... but dont know how to use them,

    i have attached my workbook. my problem is; i play a game on facebook and they have introduced something called relay towers. now these relay towers are teleporters to different sectors. the problem is... if you find a target to hit in another sector i want to write that target down on my spreadsheet so itll tell me the nearest relay tower to it. the game itself does this if you are already there so you can find your nearest tower for the return trip. but if youre not there then i need to see where the nearest is so i can get there.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    04-29-2012
    Location
    nuneaton, England
    MS-Off Ver
    Excel 2007
    Posts
    366

    Re: nearest value

    bump.......

  3. #3
    Forum Contributor
    Join Date
    04-29-2012
    Location
    nuneaton, England
    MS-Off Ver
    Excel 2007
    Posts
    366

    Re: nearest value

    i take it nobody can help me?

  4. #4
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: nearest value

    I think you need to elaborate some more. I looked at your workbook and to me it's not obvious what you want to do. Reposting the workbook with more comments and descriptions of what you want to do would be the best.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  5. #5
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: nearest value

    -----------

  6. #6
    Forum Contributor
    Join Date
    04-29-2012
    Location
    nuneaton, England
    MS-Off Ver
    Excel 2007
    Posts
    366

    Re: nearest value

    ok, basically the game i play on fb is based in the ocean with islands dotted around. all these islands have their own coordinates (mine is 141632,236). the aim of the game is to build a better island and to do that you need more resources which you steal from other people. so if you find someone to steal from and hover your mouse over their base it will tell you their coordinates which you sail to. with the introduction of these relay towers you can use them to save you some sailing time. but if you dont know the nearest tower to your target you are stuck with the sailing time,

    my spreadsheet will have a list of these towers that i found or worked out but what i want to do is a have an input area and then the excel spreadsheet doing a lookup to tell me where the nearest tower to my target is.

    i will upload my current spreadsheet that i have been working on since the original attachment
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: nearest value

    I find that an important part of programming is being able to break a large problem down into small steps. I can't tell from your spreadsheet how you are approaching the problem, so it is hard for me to tell where you are stuck. Here's how I would probably break the problem down.

    1) The ancient Babylonians used a sexagesimal number system and were probably very good at doing math with it. However, our math is built on a decimal number system. The first thing I'd do with this is add formulas to convert all degree/min/sec values into decimal degrees (=deg+min/60+sec/60/60).
    2) Use the distance formula (if you don't remember it from algebra geometry http://www.purplemath.com/modules/distform.htm) to calculate the distance from a given coordinate to each tower.
    3) Then the =MIN() function would give you the closest distance. A lookup function (VLOOKUP or INDEX(Match()) or other lookup function) would be able to return the tower corresponding to that minimum distance.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  8. #8
    Forum Contributor
    Join Date
    04-29-2012
    Location
    nuneaton, England
    MS-Off Ver
    Excel 2007
    Posts
    366

    Re: nearest value

    wow, thats deep maths

  9. #9
    Forum Contributor
    Join Date
    04-29-2012
    Location
    nuneaton, England
    MS-Off Ver
    Excel 2007
    Posts
    366

    Re: nearest value

    the way i was going was to split the coordinates into 3 lots of 3 number 141632,236 would become 141-632-236 then look for the closest match vertical ( north, central and south tower) then once its found the closest one was to see if the east or west tower was closer..... at the moment i can do this. but i have to put the east and west tower in manually after it has done the vertical look up as it gets confused.

    i used index match and it was working, but i want to be able to enter the coordinates, press enter and it do it for me automatically without any further input.

  10. #10
    Forum Contributor
    Join Date
    04-29-2012
    Location
    nuneaton, England
    MS-Off Ver
    Excel 2007
    Posts
    366

    Re: nearest value

    i changed all my formulas around so the east, west coords were calculated from the result of the vertical and used a second calculation to match the first

+ 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