Possible INDEX / AGGREGATE / AVERAGE functions, involving Latitude / Longitude

ollyhughes1982

Active Member
Hi all,

Further to my recentIy solved problem (INDEX AGGREGATE issue), I have some very similar calculations that I need to solve in the red texted cells in the file linked below. If anyone might be able to help, please:

Link: parkrun - My parkrun Record - WORKING.xlsx

1. Cell C37 should display the cumulative distance I have travelled between all of the parkrun events that I have completed. According to the Running Challenges app this figure should be 10,932K. So the result should be a figure around this amount. This calculation should search the ‘All Completed Runs’ worksheet and calculate the cumulative distance between all 226 events. i.e. range C3:C2002.
2. Cell C39 should display my average latitude / longitude. According to the Running Challenges app the result should be 51.63380, -2.79525. I guess this calculation should average the BO3:BO2002 and BP3:BP2002 ranges and concatenate them, in the ‘All Completed Runs’ worksheet.
3. Cell C40 should display my average event. According to the Running Challenges app the result should be Rogiet parkrun. This calculation give the parkrun located closest to the latitude / longitude calculated in 2.
4. Cell C44 should display the event that I have completed which is furthest from my home parkrun. According to the Running Challenges app the result should be Tokoinranta parkrun.

@Fluff & @KRice are familiar with this workbook and have been massively helpful so far.

Last edited:

KRice

Well-known Member
I wanted to ask about the distances traveled. I see that your "home" parkrun has changed. To me, it would make sense to change the reference location to be your home parkrun at the time you ran in a particular event. That doesn't make a huge difference in the distance calculations (because the former and current homes are relatively close), but it would seem to be more accurate. I added a number of columns to the All Completed Runs worksheet to make those calculations: pulling in your home at the time of an event and then computing the traveled distance...as well as making the calculations for average location (lat/long) and then searching for the event venue closest to that average location. I made those edits to the previous version of the file, but you should be able to pull that information in easily. I think I'll have to upload the file to Dropbox or something like that due to size and to preserve the structure. I'll do that soon.

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

ollyhughes1982

Active Member
OK. How do I put the two to
I wanted to ask about the distances traveled. I see that your "home" parkrun has changed. To me, it would make sense to change the reference location to be your home parkrun at the time you ran in a particular event. That doesn't make a huge difference in the distance calculations (because the former and current homes are relatively close), but it would seem to be more accurate. I added a number of columns to the All Completed Runs worksheet to make those calculations: pulling in your home at the time of an event and then computing the traveled distance...as well as making the calculations for average location (lat/long) and then searching for the event venue closest to that average location. I made those edits to the previous version of the file, but you should be able to pull that information in easily. I think I'll have to upload the file to Dropbox or something like that due to size and to preserve the structure. I'll do that soon.
Great. Thank you very much

ollyhughes1982

Active Member
I have an updated latest version of my file, where I have added some columns re average etc. and have gotten to my latest point re calculations etc. Please could I upload this version and let you incorporate the part where the changing home parkrun value is incorporated? One Drive seems to limit my file size to 5mb. I might try something else, so I don't have to cut it down at all.

KRice

Well-known Member

I'll have a look. If file size is an issue, you might try uploading to Dropbox and then posting a link.

KRice

Well-known Member
I think that worked. I'll have a look and transfer over what I have in the earlier version.

ollyhughes1982

Active Member

Yes, the size doesn't seem to be an issue with Google Drive. Thanks.

KRice

Well-known Member
Give this a try...

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".

ollyhughes1982

Active Member
Wow, very complex and detailed. Thanks very much for looking at this. I will try and go though it all today. Have you overwritten the file?

ollyhughes1982

Active Member
I used the method of averaging the longitudes and latitudes as that is how the official app calculates the average, so thought best to use this

Replies
9
Views
64
Replies
14
Views
450
Replies
17
Views
574
Replies
2
Views
432
Replies
13
Views
2K

1,127,360
Messages
5,624,244
Members
416,018
Latest member
mirceaon

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.

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