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

ollyhughes1982

Active Member
Joined
Nov 27, 2018
Messages
283
Office Version
  1. 365
Platform
  1. MacOS
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.
 
Last edited:

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

ollyhughes1982

Active Member
Joined
Nov 27, 2018
Messages
283
Office Version
  1. 365
Platform
  1. MacOS
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:
Screenshot 2020-04-21 at 23.41.31.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,731
Office Version
  1. 365
Platform
  1. Windows
For the first one try
=SUM(SUMIFS('Miscellaneous Data - AP'!I3:I2003,'Miscellaneous Data - AP'!B3:B2003,'All Completed Runs'!C3:C2002))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,731
Office Version
  1. 365
Platform
  1. Windows
Part 4 can be done with pretty much the same formula as your last post.
 

ollyhughes1982

Active Member
Joined
Nov 27, 2018
Messages
283
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

Great. Thanks 👍🏻
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,731
Office Version
  1. 365
Platform
  1. Windows
You're welcome
 

ollyhughes1982

Active Member
Joined
Nov 27, 2018
Messages
283
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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

Well-known Member
Joined
Dec 9, 2003
Messages
1,077
Office Version
  1. 2019
Platform
  1. Windows
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"
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,077
Office Version
  1. 2019
Platform
  1. Windows
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,127,991
Messages
5,628,007
Members
416,286
Latest member
ko15

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top