+ Reply to Thread
Results 1 to 8 of 8

Distance rate calculator

  1. #1
    Registered User
    Join Date
    11-18-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    16

    Distance rate calculator

    Hello I was wondering if someone could help me to create a distance calculator. At present I have a spreadsheet using vlookup formulas so where a person can choose a town froma drop down list and the distance to that town from a particular suburb will automatically be displayed with the vlookup formula.

    However I want to add to this so the person can first choose a different from suburb then choose the TO suburb. For example at present my spreadhseet will only show the distance from Melbourne to the suburb chosen from the drop down list.
    I want to be able to first choose the departure town so it might be Melbourne or Sydney or Perth, then the destination town and depending on this the distance between the two is shown in the cell.
    I have a file containing my departure towns and the distances to each suburb from them.
    Im just not sure how to do the first part so my vlookup formula for the destination will use a different range depending on what departure town was selected.
    Any help would be appreciated.

    Thanks heaps

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: help creating a distance rate calculator?

    You'll need dependent validation list.

    Look here:

    http://www.contextures.com/xlDataVal02.html

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: help creating a distance rate calculator?

    Actually, I think this is a simple two dimensional INDEX/MATCH problem.

    Take a look at this sample sheet and particularly the two-dimensional table. If you put your distance table into a standard 2D table like that (I've seen many distance maps laid out this way over the years), then this should work nicely for you.

    INDEX-MATCH-Simple.xls

    NOTE: the "table" is on the second sheet. Instead of Colors and Animals, you would just have two sets of city names.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: help creating a distance rate calculator?

    Another example:
    Attached Files Attached Files

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: help creating a distance rate calculator?

    Thanks WHER, that's a great standard example of a distance matrix. Do you mind if I incorporate that into my sample sheet permanently?

    =======
    Truman,

    If these suggestions takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

  6. #6
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: Distance rate calculator

    It would be an honour, sir.
    Seriously, this was a task/assignment during an Excel course i took last year.

  7. #7
    Registered User
    Join Date
    11-18-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Distance rate calculator

    Thanks for the help from everyone. I can use the dependant data validation to select a suburb destination depending on what departure point I select however Im not sure what formulas to use to show my distances. My suburb lists are very long with hundreds of suburbs so I cant set it up similar to the distances 2d table example shown my Jbeaucaire.
    And as we have a limit of 200kms some towns are more than 200kms from the departure point so are not in the list so each departure point has a different destination list. Ive included an example here but its just a short selection of my suburb list.
    If I had the same suburbs for each destination I could have it setup as per distance2.xls and then could use the INDEX/INDEX/MATCH with named ranges as per Jbeaucaires example.
    Thanks again for any help.

    Just to explain my example file distance.xls as Adams estate is 165 kms from CAM its in the distance list, but as its more than 200kms from CLAY and HAL its not in their lists........So increase your distances I hear someone say....problem is no matter what distance we use there is always going to be suburbs that are out of range from one or more of our departure towns....Hope Im making sense...LOL
    Attached Files Attached Files
    Last edited by truman40; 11-19-2009 at 11:43 PM. Reason: Explain example

  8. #8
    Registered User
    Join Date
    11-18-2009
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Distance rate calculator

    Bump....Someone???? Anyone??????

+ 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