# 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:

### 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
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

##### MrExcel MVP, Moderator
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
Part 4 can be done with pretty much the same formula as your last post.

Great. Thanks

You're welcome

#### ollyhughes1982

##### Active Member

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

Thanks

#### KRice

##### Well-known Member
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))

Replies
9
Views
64
Replies
14
Views
453
Replies
17
Views
594
Replies
2
Views
436
Replies
13
Views
2K

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.

### Which adblocker are you using?

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