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:

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,071
Office Version
  1. 2019
Platform
  1. Windows
I inserted formulas in the file you provided and saved with a new name and then uploaded. About the averaging...then the rightmost column in the All Completed Runs sheet would need some editing to use avg. lat/long from the preferred method. Right now, that column uses the avg lat/long from the avg Cartesian coordinates...not a big deal to change.
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,071
Office Version
  1. 2019
Platform
  1. Windows
To ensure that the preferred average location coordinates are used when estimating the distance of each event from that average location, you might add an extra column to the All Completed Runs sheet, as shown here:
parkrun - My parkrun Record - WORKING_rev20200422.xlsx
CGCHCICJCKCLCMCNCOCPCQ
17082.753416157 / 226 = 051.5607683-3.03532010.61980565-0.03044050.783658551.6254859-2.8117115Rogiet parkrunRogiet parkrun
2Distance Traveled to Event relative to home parkrun at time of event (km)Home parkrun at time event was runHome Latitude (Decimal Degrees)Home Longitude (Decimal Degrees)X1 for event locationY1 for event locationZ1 for event locationAvg LatAvg LonDistance from Event to Avg Location (based on avg Cart. Coord.) (km)Distance from Event to Avg Location (based on avg lat/long) (km)
30Newport parkrun51.559479-3.0309390.62083219-0.03287260.7832539716.829057517.6447424
40Newport parkrun51.559479-3.0309390.62083219-0.03287260.7832539716.829057517.6447424
50Newport parkrun51.559479-3.0309390.62083219-0.03287260.7832539716.829057517.6447424
60Newport parkrun51.559479-3.0309390.62083219-0.03287260.7832539716.829057517.6447424
All Completed Runs
Cell Formulas
RangeFormula
CG1CG1=SUBTOTAL(9,CG3:CG2002)
CH1CH1=COUNTIF(CG3:CG2002,0)&" / "&COUNT(CG3:CG2002) & " = 0"
CI1:CM1CI1=SUBTOTAL(1,CI3:CI2002)
CN1CN1=ATAN2(SQRT(CK1^2+CL1^2),CM1)*180/PI()
CO1CO1=ATAN2(CK1,CL1)*180/PI()
CP1CP1=INDEX(C3:C2002,MATCH(AGGREGATE(15,6,CP3:CP2002,1),CP3:CP2002,0))
CQ1CQ1=INDEX(C3:C2002,MATCH(AGGREGATE(15,6,CQ3:CQ2002,1),CQ3:CQ2002,0))
CG3:CG6CG3=IFERROR(6371*(ACOS(SIN(CI3*PI()/180)*SIN(BO3*PI()/180)+COS(CI3*PI()/180)*COS(BO3*PI()/180)*COS((CJ3-BP3)*PI()/180))),"")
CH3:CH6CH3=IFNA(VLOOKUP($E3,'All Completed Runs - Summary'!$F$6:$K$7,3),"")
CI3:CI6CI3=IFNA(VLOOKUP($E3,'All Completed Runs - Summary'!$F$6:$K$7,5),"")
CJ3:CJ6CJ3=IFNA(VLOOKUP($E3,'All Completed Runs - Summary'!$F$6:$K$7,6),"")
CK3:CK6CK3=IFERROR(COS($BO3*PI()/180)*COS($BP3*PI()/180),"")
CL3:CL6CL3=IFERROR(COS($BO3*PI()/180)*SIN($BP3*PI()/180),"")
CM3:CM6CM3=IFERROR(SIN($BO3*PI()/180),"")
CP3:CP6CP3=IFERROR(6371*ACOS(SIN($CN$1*PI()/180)*SIN($BO3*PI()/180) + COS($CN$1*PI()/180)*COS($BO3*PI()/180)*COS($BP3*PI()/180 - $CO$1*PI()/180)),"")
CQ3:CQ6CQ3=IFERROR(6371*ACOS(SIN($BO$1*PI()/180)*SIN($BO3*PI()/180) + COS($BO$1*PI()/180)*COS($BO3*PI()/180)*COS($BP3*PI()/180 - $BP$1*PI()/180)),"")


For convenience, I unmerged the top row 1 spanning your columns of data in the All Completed Runs sheet and inserted formulas there to compute the average lat/long in the cells directly above the column heading. That makes it convenient for referencing in the column CQ formula above.
parkrun - My parkrun Record - WORKING_rev20200422.xlsx
BOBP
151.60341483-2.785559208
2Latitude (Decimal Degrees)Longitude (Decimal Degrees)
351.559479-3.030939
451.559479-3.030939
All Completed Runs
Cell Formulas
RangeFormula
BO1:BP1BO1=AVERAGE(BO3:BO2002)
BO3:BO4BO3=IFERROR(VLOOKUP(C3,'Miscellaneous Data - AP'!$B$3:$E$2002, 4, FALSE),"")
BP3:BP4BP3=IFERROR(VLOOKUP(C3,'Miscellaneous Data - AP'!$B$3:$F$2002, 5, FALSE),"")


Then check the helper table on 'All Completed Runs - Summary'. It was working fine, but I must have inadvertently changed something because I noticed some incorrect results that depend on those cells. The corrected version is here:
parkrun - My parkrun Record - WORKING_rev20200422.xlsx
FGHIJK
4Helper table
5first runlast runHome parkrun historyHomelatitude (dec deg)longitude (dec deg)
64064243820Newport parkrun151.559479-3.030939
74384843890Coed Cefn-pwll-du parkrun251.591855-3.140954
All Completed Runs - Summary
Cell Formulas
RangeFormula
F6F6=MINIFS('All Completed Runs'!E3:E2002,'All Completed Runs'!C3:C2002,H6,'All Completed Runs'!E3:E2002,"<="&DATE(2020,1,17))
G6G6=MAXIFS('All Completed Runs'!E3:E2002,'All Completed Runs'!C3:C2002,H6,'All Completed Runs'!E3:E2002,"<="&DATE(2020,1,17))
F7F7=MINIFS('All Completed Runs'!E3:E2002,'All Completed Runs'!C3:C2002,H7,'All Completed Runs'!E3:E2002,">="&DATE(2020,1,18))
G7G7=MAXIFS('All Completed Runs'!E3:E2002,'All Completed Runs'!C3:C2002,H7,'All Completed Runs'!E3:E2002,">="&DATE(2020,1,18))
J6:J7J6=INDEX('Miscellaneous Data - AP'!$B$3:$F$2002,MATCH($H6,'Miscellaneous Data - AP'!$B$3:$B$2002,0),4)
K6:K7K6=INDEX('Miscellaneous Data - AP'!$B$3:$F$2002,MATCH($H6,'Miscellaneous Data - AP'!$B$3:$B$2002,0),5)


Finally, the formulas in the yellow cells on the 'All Completed Runs - Summary' sheet change as shown here to point to the CQ column in the 'All Completed Runs' sheet to use your preferred average lat/long values:
Cell Formulas
RangeFormula
C37C37=TEXT('All Completed Runs'!CG1,0) & " km (note that " & 'All Completed Runs'!CH1 &" relative to home parkrun)"
C38C38=TEXT('All Completed Runs'!CG1*0.621371,0) & " mi (note that " & 'All Completed Runs'!CH1 &" relative to home parkrun)"
C39C39=TEXT('All Completed Runs'!CN1,"0.0000") & ", " & TEXT('All Completed Runs'!CO1,"0.0000")
B37B37=TEXT(ROUND(SUM(SUMIFS('Miscellaneous Data - AP'!I3:I2003,'Miscellaneous Data - AP'!B3:B2003,'All Completed Runs'!C3:C2002)),0),"#,##0")&"K"
B38B38=TEXT(ROUND(SUM(SUMIFS('Miscellaneous Data - AP'!J3:J2003,'Miscellaneous Data - AP'!B3:B2003,'All Completed Runs'!C3:C2002)),0),"#,##0")&" Miles"
B39B39=ROUND(AVERAGE('All Completed Runs'!BO3:BO2002),6)&", "&ROUND(AVERAGE('All Completed Runs'!BP3:BP2002),6)
B40B40="closest (" & TEXT(AGGREGATE(15,6,'All Completed Runs'!CQ3:CQ2002,1),0) & " km) to " & INDEX('All Completed Runs'!C3:C2002,MATCH(AGGREGATE(15,6,'All Completed Runs'!CQ3:CQ2002,1),'All Completed Runs'!CQ3:CQ2002,0)) & ", " & INDEX('All Completed Runs'!BR3:BR2002,MATCH(AGGREGATE(15,6,'All Completed Runs'!CQ3:CQ2002,1),'All Completed Runs'!CQ3:CQ2002,0))
B41B41=TEXT(INDEX('Miscellaneous Data - AP'!I3:I2002,MATCH(INDEX('All Completed Runs'!C3:C2002,MATCH(AGGREGATE(15,6,'All Completed Runs'!CQ3:CQ2002,1),'All Completed Runs'!CQ3:CQ2002,0)),'Miscellaneous Data - AP'!B3:B2002,0)),0) & " km away"
B42B42=TEXT(INDEX('Miscellaneous Data - AP'!I3:I2002,MATCH(INDEX('All Completed Runs'!C3:C2002,MATCH(AGGREGATE(15,6,'All Completed Runs'!CQ3:CQ2002,1),'All Completed Runs'!CQ3:CQ2002,0)),'Miscellaneous Data - AP'!B3:B2002,0))*0.621371,0) & " mi away"
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 

ollyhughes1982

Active Member
Joined
Nov 27, 2018
Messages
283
Office Version
  1. 365
Platform
  1. MacOS
I have just noticed my functions in cells B43:B46 aren't looking in for events that are completed (a 1 column K int the first worksheet). How can I add that in, please?
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,071
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

About your last question...is it okay to use information from the 'Miscellaneous Data - AP' sheet? That sheet appears to list the venues only once (in column B) and the distances traveled (columns I:J) are based on the location of the present-day home parkrun, not necessarily the home parkrun at the time of the run. This issue was mentioned in posts #11 and #18, and could yield errors if your home parkrun changes:
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.
Did you make a decision about this issue? One option is to ignore the home parkrun / distance error altogether, but then if and when you move, and then declare a new home parkrun, you will have to modify the distance formulas in 'Miscellaneous Data - AP'!I:L to point to the new home parkrun. The other approach could examine distance traveled in the 'All Completed Runs' sheet. On that sheet, each venue may be listed more than once (e.g., if you run that course more than once). But a search of 'All Completed Runs'!CG3:CG2002 would find the maximum traveled distance relative to the home parkrun at the time of the event, and that value relies on a formula that automatically examines the date of the run and uses the lat/long coordinates of the home parkrun at that time. A working example of that is shown in 'All Completed Runs - Summary'!C43:C46. You'll notice a distance discrepancies comparing C45:C46 to B45:B46, and that is due to the B formulas using distances relative to your current home parkrun, while the C formulas use distances relative to your former home parkrun (which was the "home" parkrun when the run occurred).

Cell Formulas
RangeFormula
B43B43=INDEX('Miscellaneous Data - AP'!$G$3:$G$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))
C43C43=INDEX('All Completed Runs'!BQ3:BQ2002,MATCH(AGGREGATE(14,6,'All Completed Runs'!CG3:CG2002,1),'All Completed Runs'!CG3:CG2002,0))
B44B44=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))
C44C44=INDEX('All Completed Runs'!C3:C2002,MATCH(AGGREGATE(14,6,'All Completed Runs'!CG3:CG2002,1),'All Completed Runs'!CG3:CG2002,0)) & ", " & INDEX('All Completed Runs'!BR3:BR2002,MATCH(AGGREGATE(14,6,'All Completed Runs'!CG3:CG2002,1),'All Completed Runs'!CG3:CG2002,0))
B45B45=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)) & " - " &TEXT(ROUND(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),"#,##0") &"K Away"
C45C45=INDEX('All Completed Runs'!C3:C2002,MATCH(AGGREGATE(14,6,'All Completed Runs'!CG3:CG2002,1),'All Completed Runs'!CG3:CG2002,0)) & ", " & INDEX('All Completed Runs'!BR3:BR2002,MATCH(AGGREGATE(14,6,'All Completed Runs'!CG3:CG2002,1),'All Completed Runs'!CG3:CG2002,0)) & " - " & TEXT(INDEX('All Completed Runs'!CQ3:CQ2002,MATCH(AGGREGATE(14,6,'All Completed Runs'!CG3:CG2002,1),'All Completed Runs'!CG3:CG2002,0)),"#,##0") & " K Away"
B46B46=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'!$J$3:$J$2002)/('Miscellaneous Data - AP'!$J$3:$J$2002>0),1),'Miscellaneous Data - AP'!$J$3:$J$2002,0)) & " - " &TEXT(ROUND(INDEX('Miscellaneous Data - AP'!$J$3:$J$2002,MATCH(AGGREGATE(14,6,('Miscellaneous Data - AP'!$J$3:$J$2002)/('Miscellaneous Data - AP'!$J$3:$J$2002>0),1),'Miscellaneous Data - AP'!$J$3:$J$2002,0)),0),"#,##0") &" Miles Away"
C46C46=INDEX('All Completed Runs'!C3:C2002,MATCH(AGGREGATE(14,6,'All Completed Runs'!CG3:CG2002,1),'All Completed Runs'!CG3:CG2002,0)) & ", " & INDEX('All Completed Runs'!BR3:BR2002,MATCH(AGGREGATE(14,6,'All Completed Runs'!CG3:CG2002,1),'All Completed Runs'!CG3:CG2002,0)) & " - " & TEXT(INDEX('All Completed Runs'!CQ3:CQ2002,MATCH(AGGREGATE(14,6,'All Completed Runs'!CG3:CG2002,1),'All Completed Runs'!CG3:CG2002,0))*0.621371,"#,##0") & " Miles Away"
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Note: after previewing this, I noticed the B formulas mentioned are showing up as array formulas. I don't think they need to be entered as array formulas, as a conventional INDEX/MATCH should work, but the main question is...Is there any reason why this approach won't work? Are there any issues with junior events showing up in the 'All Completed Runs' sheet that you do not want to consider?

Here is a revised version of the full five showing the addition of the formulas in C43:C46
 

ollyhughes1982

Active Member
Joined
Nov 27, 2018
Messages
283
Office Version
  1. 365
Platform
  1. MacOS
This is great work. Thanks so much. I thin kI do want to allow for the changes in home parkrun, although I think it unlikely this will ever change again. But I do want to be able to calculate accurately according to the home run at the time, so want to to do it that way. Thanks again for all of your help.
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,071
Office Version
  1. 2019
Platform
  1. Windows
You're welcome...glad to help. Post back if something needs attention.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,777
Messages
5,626,820
Members
416,202
Latest member
donya ba

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