Mathematically challenged, not an Excel guru, need help
February 13, 2015 8:47 AM   Subscribe

I am trying to figure out a way to use Excel to take an initial lat/long coordinate and then add a pre-determined figure to create offset coordinate points.

EG: IP lat/long
add X to create offset
add X to create offset
and so on.

Kind of time sensitive so I figured I would throw it out here in the event that someone has done this. My initial search on the net is bringing up a lot of info on calculating between points. I just want to take a point and add an offset, more likely 8 additional offsets. My initial offsets will be longitudinal.

Thanks for any assistance provided.
posted by a3matrix to Science & Nature (4 answers total) 1 user marked this as a favorite
 
I'm not quite clear what you're asking for. Is the offset a constant length on the Earth's surface, or a constant shift in latitude/longitude?
posted by Johnny Assay at 8:52 AM on February 13, 2015


A constant shift in longitude. A base lat/long then add a predetermined distance. I am trying to create nine points equidistant across a 1 KM line. Initially an east-west line. I want to be able to put the first lat/long in and have excel calculate the other 8 points and generate the lat/long for them.
The results then uploaded/imported to Earthpoint and viewed on google earth.
Thank you.
posted by a3matrix at 8:57 AM on February 13, 2015


If you're looking to shift a distance Lx to the East and Ly to the north along the Earth's surface, then the changes in latitude λ and longitude φ will be

∆λ = Ly * 360/(6371 km * 2π)
∆φ = Lx * 360/(sin(λ) * 6371 km * 2π)

These will give answers in degrees; if for some reason you want answers in radians, then leave out the 360's and 2π's. I'm not sure if Excel expects the argument to its sine function to be in radians or degrees, but if it expects input in degrees, then you'll have to do a similar compensation there.

(Note that by "to the North" and "to the East" above, I mean "along a longitude line" and "along a latitude line". Latitude lines aren't really straight lines on the Earth's surface, since they're not great circles; but I assume that your distances aren't great enough to need to worry about that.)
posted by Johnny Assay at 9:07 AM on February 13, 2015


Thanks for your help Johnny. The last piece of the puzzle I have is how to apply it to an excel sheet. As stated in my post title, I am not an excel guru.
I appreciate your time and assistance.
a3
posted by a3matrix at 9:38 AM on February 13, 2015


« Older Smartphones for Dummies   |   Chinese/Taishanese translation for short bits of a... Newer »
This thread is closed to new comments.