Geographic Midpoints in Excel: A Guide – CardBoredom: Baseball Card Blog

So you’ve read my post about finding the geographic midpoint of assorted baseball stadiums and player hometowns, and now you want to know how to do it yourself. We’re going to get along great.

Before we begin, a brief programming note: The following assumes the Earth is a sphere. The Earth is in fact not a perfect sphere, but rather an ellipsoid. That means navigational math is a bit messier in the real world, but an assumption of a spherical shape will work good enough for our purposes. We’re just trying to approximate a location, not set an aircraft down on a rooftop in dense fog.

Setup

Let’s assume you want to find the midpoint for two locations: Babe Ruth’s Baltimore home at St. Mary’s Industrial School and his grave in West Chester, New York. Set up your workbook as follows:

	A	B	C	D	E	F	G	H
1	Place	Latitude	Longitude	Lat (Radians)	Lon (Radians)	Cartesian X	Cartesian Y	Cartesian Z
2	School
3	Grave
4	Average
5	Hyp.
6	Midpoint

Both Column A and Row 1 represent labels that will be used to help us better interpret the worksheet. Your inputs will go into cells B2:C3 with all remaining cells to be filled in with calculations. Amber text will be used on this page to indicate where changes have been made from one step to another.

Enter Coordinates

This example will require the entry of geographic coordinates in decimal format and will run all calculations in the same manner. Latitude is relative to the equator, with northern hemisphere coordinates denoted by positive values and negative values for those in the southern hemisphere. Longitude likewise has a positive/negative association with direction. Positive values represent the eastern hemisphere and negative figures indicate locations in the west.

	A	B	C	D	E	F	G	H
1	Place	Latitude	Longitude	Lat (Radians)	Lon (Radians)	Cartesian X	Cartesian Y	Cartesian Z
2	School	39.2736307	-76.6686491
3	Grave	41.0903644	-73.7969623
4	Average
5	Hyp.
6	Midpoint

Convert to Radians

Finding decimalized coordinates is generally easy to do, either through direct conversion [Decimal Degrees = Degrees + (Minutes + Seconds/60)/60] or by just plugging the degree coordinates into Google Maps.

The calculations that we will perform, however, need to be done in radians. To perform this conversion, you will multiply the decimal degree coordinates by (π/180).

The formulas below should be entered into the indicated cells (D2:E3).

Cell D2:  =B2*(PI()/180)
Cell D3:  =B3*(PI()/180)
Cell E2:  =C2*(PI()/180)
Cell E3:  =C3*(PI()/180)

The worksheet below displays in amber the output that should appear after entering these formulas.

	A	B	C	D	E	F	G	H
1	Place	Latitude	Longitude	Lat (Radians)	Lon (Radians)	Cartesian X	Cartesian Y	Cartesian Z
2	School	39.2736307	-76.6686491	0.68545416	(1.33812036)
3	Grave	41.0903644	-73.7969623	0.71716215	(1.28799997)
4	Average
5	Hyp.
6	Midpoint

Cartesian Coordinates

With radians calculated for each of the coordinates, their relative position can begin to be plotted relative to the middle of a sphere. The following formulas can be entered in cells F2:H3 to generate X,Y, and Z coordinates.

Cell F2:  =COS(D2)*COS(E2)
Cell F3:  =COS(D3)*COS(E3)
Cell G2:  =COS(D2)*SIN(E2)
Cell G3:  =COS(D3)*SIN(D3)
Cell H2:  =SIN(D2)
Cell H3:  =SIN(D3)

Once again, the output of these formulas is shown below in amber.

	A	B	C	D	E	F	G	H
1	Place	Latitude	Longitude	Lat (Radians)	Lon (Radians)	Cartesian X	Cartesian Y	Cartesian Z
2	School	39.2736307	-76.6686491	0.68545416	(1.33812036)	0.178501	-0.753270988	0.633025
3	Grave	41.0903644	-73.7969623	0.71716215	(1.28799997)	0.210307	-0.723737171	0.657249
4	Average
5	Hyp.
6	Midpoint

Averages

Average the values of each cartesian coordinate in cells F4:H4.

Cell F4:  =AVERAGE(F2:F3)
Cell G4:  =AVERAGE(G2:G3)
Cell H4:  =AVERAGE(H2:H3)

	A	B	C	D	E	F	G	H
1	Place	Latitude	Longitude	Lat (Radians)	Lon (Radians)	Cartesian X	Cartesian Y	Cartesian Z
2	School	39.2736307	-76.6686491	0.68545416	(1.33812036)	0.178501	-0.753270988	0.633025
3	Grave	41.0903644	-73.7969623	0.71716215	(1.28799997)	0.210307	-0.723737171	0.657249
4	Average					0.194404	-0.738504079	0.645137
5	Hyp.
6	Midpoint

Throwing it in Reverse: Converting Cartesian Back to Radians

The purpose of everything done in the previous steps was to find the geographic midpoint. Technically you now have the answer to the initial question, but it is in a format that makes no sense on map. To make use of the information it must be converted back to the familiar latitude/longitude format. We’re going to work backwards.

Enter the following formulas to generate the midpoint in terms of radians, which will be converted back to decimal degrees in the following step.

Cell E6:  ATAN2(F4,G4)
Cell E5:  SQRT(F4*F4+G4*G4)
Cell D6:  ATAN2(E5,H4)

	A	B	C	D	E	F	G	H
1	Place	Latitude	Longitude	Lat (Radians)	Lon (Radians)	Cartesian X	Cartesian Y	Cartesian Z
2	School	39.2736307	-76.6686491	0.68545416	(1.33812036)	0.178501	-0.753270988	0.633025
3	Grave	41.0903644	-73.7969623	0.71716215	(1.28799997)	0.210307	-0.723737171	0.657249
4	Average					0.194404	-0.738504079	0.645137
5	Hyp.				0.7636630
6	Midpoint			0.701462938	(1.3133958)

Radians Back to Decimal Degrees

Walking it back for one last step, the radian version of latitude and longitude in the previous step can be readily converted back to decimal degrees. The output will have the same syntax as in the initial setup of the worksheet with positive values for the Northern and Eastern hemispheres and negative values in the South and West.

Cell B6:  =D6*(180/PI())
Cell C6:  =E6*(180/PI())

	A	B	C	D	E	F	G	H
1	Place	Latitude	Longitude	Lat (Radians)	Lon (Radians)	Cartesian X	Cartesian Y	Cartesian Z
2	School	39.2736307	-76.6686491	0.68545416	(1.33812036)	0.178501	-0.753270988	0.633025
3	Grave	41.0903644	-73.7969623	0.71716215	(1.28799997)	0.210307	-0.723737171	0.657249
4	Average					0.194404	-0.738504079	0.645137
5	Hyp.				0.7636630
6	Midpoint	40.19086583	-75.25203602	0.701462938	(1.3133958)

The resulting midpoint is 40.19086583 North, 75.25203602 West, which happens to be in the front yard of a residence in Gwynedd Valley, Pennsylvania.