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

[ollyhughes1982]

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.

Thanks in advance!

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

 

[ollyhughes1982]

Re calculation 1: Distances from home are event are located in the range I3:I2002 of the 'Miscellaneous Data - AP' worksheet.

From the Running Challenges app:

 

[Fluff]

For the first one try
=SUM(SUMIFS('Miscellaneous Data - AP'!I3:I2003,'Miscellaneous Data - AP'!B3:B2003,'All Completed Runs'!C3:C2002))

 

[Fluff]

Part 4 can be done with pretty much the same formula as your last post.

 

[ollyhughes1982]

Great. Thanks ??

 

[Fluff]

You're welcome

 

[ollyhughes1982]

Part 4 can be done with pretty much the same formula as your last post.

Apologies @Fluff , I'm struggling a bit to convert this for my furthest travelled event. You said I could amend my previous formula:

=TEXTJOIN(", ",,INDEX(SORT(FILTER('Miscellaneous Data - AP'!B3:J2001,('Miscellaneous Data - AP'!U3:U2001=1)*('Miscellaneous Data - AP'!K3:K2001<>1)*('Miscellaneous Data - AP'!I3:I2001>0)),8,1),1,{1,3}))

I'm just struggling to convert it to furthest away instead of closest and it should be completed events, rather than one I haven't completed yet.

Thanks.

 

[KRice]

Here is one way using Excel 2019...this finds the "furthest travelled event" in km:
=TEXT(INDEX('Miscellaneous Data - AP'!$I$3:$I$2002,MATCH(AGGREGATE(14,6,('Miscellaneous Data - AP'!$I$3:$I$2002)/('Miscellaneous Data - AP'!$I$3:$I$2002>0),1),'Miscellaneous Data - AP'!$I$3:$I$2002,0)),0) & " km away"

 

[ollyhughes1982]

Thanks ??

 

[KRice]

This gives the furthest location:
=INDEX('Miscellaneous Data - AP'!$B$3:$B$2002,MATCH(AGGREGATE(14,6,('Miscellaneous Data - AP'!$I$3:$I$2002)/('Miscellaneous Data - AP'!$I$3:$I$2002>0),1),'Miscellaneous Data - AP'!$I$3:$I$2002,0)) & ", " & INDEX('Miscellaneous Data - AP'!$L$3:$L$2002,MATCH(AGGREGATE(14,6,('Miscellaneous Data - AP'!$I$3:$I$2002)/('Miscellaneous Data - AP'!$I$3:$I$2002>0),1),'Miscellaneous Data - AP'!$I$3:$I$2002,0))