# Longitude and Latitude Calculations advice

#### Reshaw

##### Board Regular
Good afternoon everyone

I have used gmaps functions to fetch a list of longitude and latitudes for a (very long) list of delivery points for one of my customers.

I am attempting to calculate the centre of gravity for the distribution (not to be confused with the centre of mass of a physical object, this is a logistics term). So I did a simple mean average of the long's and the lat's and ploted these average values into a Google maps via a Hyperlink command. this works well and seems to make a kind of sense. For example I can filter out Scottish destinations and the CofG moves southwards as you'd expect.

I read online somewhere that given the earth is not flat and here in the UK we are quite a ways above the equator, the error could be relatively large.

If I assume the earth is spherical then I should perform some kind of cosine calculation to make it more accurate...

but of course the earth is not spherical either...

So my question is, How inaccurate is the simple mean average calculation I have done?

I am trying to find a suitable transport hub on a motorway near the CofG so a couple of miles out shouldn't be a massive problem. But if I have a potential error of many 10's of miles that would be more of a problem.

Any advice, thoughts or feedback is welcome.

Regards
Rob

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
For driving distances between the arctic circles, I think you could scale x (longitude) by the cosine of the midpoint latitude (like a UTM projection), and errors due to spherical earth would be dwarfed by errors due to road networks not being point to point.

maybe Batch geocoding and then just measure or judge by eye, you can't a afford a centre thats Buckingham Palace

For driving distances between the arctic circles, I think you could scale x (longitude) by the cosine of the midpoint latitude (like a UTM projection), and errors due to spherical earth would be dwarfed by errors due to road networks not being point to point.

The gmaps functions use google maps to provide a distance by road from point to point, so i can be fairly confident the road distances are reasonably accurate.

The long lat calculations provide a centre (accuracy to be confirmed) from which i can pick a suitable location and then recalculate the road distances for each delivery point.

What i would like to know is how far off the true centre point i will be with this method

Thanks for your input, it is appreciated

There are approx 14000 movements to map out, 4000 of them unique locations. Gut feeling is usually a way off with this sort of thing.

If I were going to do this with Excel, I'd do the UTM approach to calculate the frequency-weighted center, and pick a half-dozen nice industrial parks within an x-mile radius. Then use whatever API to get driving distances, and calulate the frequency-weighted average for each possibility.

Seems like ESRI's ArcGIS should have support for exactly this problem.

If I were going to do this with Excel, I'd do the UTM approach to calculate the frequency-weighted center, and pick a half-dozen nice industrial parks within an x-mile radius. Then use whatever API to get driving distances, and calulate the frequency-weighted average for each possibility.

Seems like ESRI's ArcGIS should have support for exactly this problem.

Ok so what would the utm approach be precisely? I mean what would the formula look like?

Rob

Random points in a 4 x 4 degree area:

 A​ B​ C​ D​ E​ F​ G​ H​ I​ J​ K​ 4​ Midpoint​ 53.434​ -0.985​ B4: =SUMPRODUCT(B8:B21, \$D8:\$D21)/SUM(\$D8:\$D21) 5​ Cosine​ 0.5957​ C4: =SUMPRODUCT(C8:C21, \$D8:\$D21)/SUM(\$D8:\$D21) 6​ B5: =COS(RADIANS(B4)) 7​ Lat​ Lon​ Wgt​ Lon'​ Euc Dist [km]​ GC Dist [km]​ % Error​ 8​ 52.870​ 0.730​ 1​ 0.037​ 129.7​ 130.4​ 0.6%​ E8: =(C8-\$C\$4)*\$B\$5 + \$C\$4 9​ 52.710​ 0.632​ 4​ -0.022​ 133.9​ 134.7​ 0.6%​ F8: =111.12 * SQRT((B8-\$B\$4)^2 + (E8-\$C\$4)^2) 10​ 54.941​ -0.313​ 5​ -0.585​ 173.2​ 173.1​ -0.1%​ G8: =6371 * CentralAngle(B8, C8, \$B\$4, \$C\$4) 11​ 52.924​ -1.585​ 1​ -1.342​ 69.3​ 69.4​ 0.3%​ H8: =1-F8/G8 12​ 52.064​ -1.784​ 2​ -1.461​ 161.2​ 161.6​ 0.2%​ 13​ 51.276​ -1.303​ 1​ -1.174​ 240.8​ 241.0​ 0.1%​ 14​ 52.786​ -2.091​ 4​ -1.644​ 102.7​ 103.2​ 0.5%​ 15​ 52.895​ -0.874​ 3​ -0.919​ 60.4​ 60.4​ 0.1%​ 16​ 54.337​ -2.779​ 3​ -2.054​ 155.5​ 154.6​ -0.6%​ 17​ 54.942​ 0.479​ 3​ -0.113​ 193.5​ 192.8​ -0.4%​ 18​ 52.879​ -0.797​ 4​ -0.873​ 63.0​ 63.0​ 0.1%​ 19​ 54.161​ -1.324​ 3​ -1.187​ 83.8​ 83.8​ 0.0%​ 20​ 52.545​ -1.864​ 3​ -1.509​ 114.7​ 115.1​ 0.3%​ 21​ 54.464​ -2.019​ 1​ -1.601​ 133.3​ 133.0​ -0.3%​

Replies
6
Views
207K

1,221,418
Messages
6,159,795
Members
451,589
Latest member
Harold14

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back