geographical

Lambada · May 18th, 2002, 09:16 AM

I need to make a geographical research showing traveling distances between different locations in and outside the city. Does Excel have some specific format that used for this purpose?

Thank you.

[ This Message was edited by: Lambada on 2002-05-18 08:34 ]

WayneTN · May 18th, 2002, 07:16 PM

Are you talking about entering X and Y coordinates of various locations on a city/county map and then having XL calculate the distance between the locations???

Lambada · May 18th, 2002, 08:44 PM

Yes!

WayneTN · May 19th, 2002, 07:53 PM

Lambada,

This is an easy one then! Let's say that in the first row you have the following headings for Cols. A through E:

X1, Y1, X2, Y2, Distance

The first four headings are for the Cartesian coordinates of the two locations on your map, the last heading if for the distance between the locations. Let's say that Point 1's coordinates are (X1,Y1); Point 2 is (X2,Y2).

EXAMPLE:

If Point 1 is at coordinate (5,3) and Point 2 is at (-6,-2), then you would put their coordinates in row 2 in the first 4 cells: 5, 3, -6, -2.

Then put the following in Cell E2

=SQRT((A2-C2)^2+(B2-D2)^2)

Cell E2 will calculate the distance. This will work for map locations in any of the four quadrants. Remember to use positive and negative values depending on which quadrant you are in w.r.t the origin...maybe downtown. Of course, you could put the origin at the lower left point of your map and then all numbers would be positive! (All this calculation is doing is computing the value of the diagonal of an imaginary rectangle with the map locations at opposite corners.)

Hope this helps.
Wayne

Lambada · May 22nd, 2002, 07:44 PM

Thank you Wayne. My excel knowledge is very basic. You mean that excel will draw a map and calculate the distances based on the addresses I input in these cells? I really appreciate your help.

WayneTN · May 23rd, 2002, 03:51 PM

Lambada,

No, with what I have described it will not draw a map. To use the concept that I outlined, you would need to read the coordinates off of a map that YOU already have. This map would have to have some type of grid so that you could identify the location of each point of interest with respect to some grid origin.

EXAMPLE:

If your house is 5 miles east and 3 miles north of downtown (which I have assumed is at the origin of the map, i.e., the point where X=0 and Y=0), then your house would be at the point where X1=+5, Y1=+3.

Now, if you work at a building 6 miles west and 2 miles south of downtown, it's location would be the point where X2=-6, Y2=-2.

Then, if you enter these coordinates into the spreadsheet that I previously described, you would see that it is approximately 12.1 miles to work (on a straight line).

To do any more than that, it would take much more programming...

Good luck in your work.
Wayne

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 18th, 2002, 09:16 AM	#1
Lambada Board Regular Join Date: May 2002 Posts: 62	I need to make a geographical research showing traveling distances between different locations in and outside the city. Does Excel have some specific format that used for this purpose? Thank you. [ This Message was edited by: Lambada on 2002-05-18 08:34 ]

May 18th, 2002, 07:16 PM	#2
WayneTN Board Regular Join Date: May 2002 Location: Tennessee, USA Posts: 72	Are you talking about entering X and Y coordinates of various locations on a city/county map and then having XL calculate the distance between the locations???

May 18th, 2002, 08:44 PM	#3
Lambada Board Regular Join Date: May 2002 Posts: 62	Yes!

May 19th, 2002, 07:53 PM	#4
WayneTN Board Regular Join Date: May 2002 Location: Tennessee, USA Posts: 72	Lambada, This is an easy one then! Let's say that in the first row you have the following headings for Cols. A through E: X1, Y1, X2, Y2, Distance The first four headings are for the Cartesian coordinates of the two locations on your map, the last heading if for the distance between the locations. Let's say that Point 1's coordinates are (X1,Y1); Point 2 is (X2,Y2). EXAMPLE: If Point 1 is at coordinate (5,3) and Point 2 is at (-6,-2), then you would put their coordinates in row 2 in the first 4 cells: 5, 3, -6, -2. Then put the following in Cell E2 =SQRT((A2-C2)^2+(B2-D2)^2) Cell E2 will calculate the distance. This will work for map locations in any of the four quadrants. Remember to use positive and negative values depending on which quadrant you are in w.r.t the origin...maybe downtown. Of course, you could put the origin at the lower left point of your map and then all numbers would be positive! (All this calculation is doing is computing the value of the diagonal of an imaginary rectangle with the map locations at opposite corners.) Hope this helps. Wayne

May 22nd, 2002, 07:44 PM	#5
Lambada Board Regular Join Date: May 2002 Posts: 62	Thank you Wayne. My excel knowledge is very basic. You mean that excel will draw a map and calculate the distances based on the addresses I input in these cells? I really appreciate your help.

May 23rd, 2002, 03:51 PM	#6
WayneTN Board Regular Join Date: May 2002 Location: Tennessee, USA Posts: 72	Lambada, No, with what I have described it will not draw a map. To use the concept that I outlined, you would need to read the coordinates off of a map that YOU already have. This map would have to have some type of grid so that you could identify the location of each point of interest with respect to some grid origin. EXAMPLE: If your house is 5 miles east and 3 miles north of downtown (which I have assumed is at the origin of the map, i.e., the point where X=0 and Y=0), then your house would be at the point where X1=+5, Y1=+3. Now, if you work at a building 6 miles west and 2 miles south of downtown, it's location would be the point where X2=-6, Y2=-2. Then, if you enter these coordinates into the spreadsheet that I previously described, you would see that it is approximately 12.1 miles to work (on a straight line). To do any more than that, it would take much more programming... Good luck in your work. Wayne