Give this a try...
Shared with Dropbox
www.dropbox.com
I'll describe the edits and discuss some other things about the linked file:
Two sets of helper cells were used to facilitate some of the computations sought. On the 'All Completed Runs - Summary' worksheet, the range F5:K7 is used to create a small table documenting the start and end dates for any "home" parkruns in your history. The latitude and longitude of those parkruns is also summarized here. This makes it much easier to determine, based on the date of any given event, the "home" parkrun at that moment, and then the corresponding latitude and longitude of that home parkrun can be used for distance calculations.
Next, several columns were added at the far right end of the 'All Completed Runs' sheet (to avoid potential issues with other formulas). I found it interesting that your latest revision did nearly the same thing, where you added data in columns CC:CF. I added my working cells in columns CG:CP. Those additional columns summarize the home parkrun at the time of each event, and then they pull in the latitude and longitude of the home parkrun at the time of the event. With those coordinates, the distance traveled to the event is computed using the Spherical Law of Cosines. And finally, in order to estimate the "average" location, the Geographic Midpoint method described at
Geographic Midpoint Calculation Methods was used. As explained in that reference, this method converts latitude and longitude to Cartesian coordinates, assuming the x-y reference plane lies in the Earth's equatorial plane with the origin at the Earth's center and oriented such that the positive x-axis passes through the Greenwich meridian and the positive y-axis passes through the 90 degree E meridian. The positive z-axis runs from the Earth's center through the North Pole. The Earth is assumed to be spherical and have a unit radius.
Then the average position is computed by averaging all of the x's, y's, and z's to produce a composite (x,y,z) "average position", shown in CK1:CM1. Finally, the average (x,y,z) coordinates are converted back to establish an average latitude and longitude, shown in CN1:CO1. Based on the average latitude/longitude computed with this method, the distances of each event from that "average location" are computed in column CP and then searched to determine which event location is closest to the average location, and then reported in CP1. Conversions to real estimated distances use a mean Earth radius of 6371 km.
These results and formulas are then used to populate some of the cells in the summary table on the 'All Completed Runs - Summary' sheet. On that sheet, I'll call attention to rows 37-42. I see that the total distance traveled (rows 37-38) is based on this formula:
=SUM(SUMIFS('Miscellaneous Data - AP'!I3:I2003,'Miscellaneous Data - AP'!B3:B2003,'All Completed Runs'!C3:C2002)) & " km"
...but the result obtained this way includes distances that were not actually traveled because of the "home parkrun" issue. For example, 157 of the 226 completed parkruns have 0 km of travel distance associated with them because they involve runs at your home parkrun, but when your home parkrun changed from Newport to Coed Cefn-pwll-du, any prior Newport parkrun that should count as 0 travel distance instantly contributed 8.4 km to travel distance. I believe this discrepancy accounts for a cumulative difference of nearly 1400 km in total travel distance. I've added the formulas in C37:C38 for comparison to B37:B38 so that you can determine what works best for your purpose.
I see you used a location averaging method based on averaging the latitudes and longitudes (shown in B39). For comparison, results from the Geographic Midpoint method using averaged Cartesian coordinates described above are shown in C39.
I changed the text in A41:A42 to clarify that the average parkrun distance is relative to the present-day "home parkrun".
