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.

 

[KRice]

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.

 

[KRice]

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
	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ
1	7082.753416	157 / 226 = 0	51.5607683	-3.0353201	0.61980565	-0.0304405	0.7836585	51.6254859	-2.8117115	Rogiet parkrun	Rogiet parkrun
2	Distance Traveled to Event relative to home parkrun at time of event (km)	Home parkrun at time event was run	Home Latitude (Decimal Degrees)	Home Longitude (Decimal Degrees)	X1 for event location	Y1 for event location	Z1 for event location	Avg Lat	Avg Lon	Distance from Event to Avg Location (based on avg Cart. Coord.) (km)	Distance from Event to Avg Location (based on avg lat/long) (km)
3	0	Newport parkrun	51.559479	-3.030939	0.62083219	-0.0328726	0.78325397			16.8290575	17.6447424
4	0	Newport parkrun	51.559479	-3.030939	0.62083219	-0.0328726	0.78325397			16.8290575	17.6447424
5	0	Newport parkrun	51.559479	-3.030939	0.62083219	-0.0328726	0.78325397			16.8290575	17.6447424
6	0	Newport parkrun	51.559479	-3.030939	0.62083219	-0.0328726	0.78325397			16.8290575	17.6447424
All Completed Runs

Cell Formulas
Range	Formula
CG1	CG1	=SUBTOTAL(9,CG3:CG2002)
CH1	CH1	=COUNTIF(CG3:CG2002,0)&" / "&COUNT(CG3:CG2002) & " = 0"
CI1:CM1	CI1	=SUBTOTAL(1,CI3:CI2002)
CN1	CN1	=ATAN2(SQRT(CK1^2+CL1^2),CM1)*180/PI()
CO1	CO1	=ATAN2(CK1,CL1)*180/PI()
CP1	CP1	=INDEX(C3:C2002,MATCH(AGGREGATE(15,6,CP3:CP2002,1),CP3:CP2002,0))
CQ1	CQ1	=INDEX(C3:C2002,MATCH(AGGREGATE(15,6,CQ3:CQ2002,1),CQ3:CQ2002,0))
CG3:CG6	CG3	=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:CH6	CH3	=IFNA(VLOOKUP($E3,'All Completed Runs - Summary'!$F$6:$K$7,3),"")
CI3:CI6	CI3	=IFNA(VLOOKUP($E3,'All Completed Runs - Summary'!$F$6:$K$7,5),"")
CJ3:CJ6	CJ3	=IFNA(VLOOKUP($E3,'All Completed Runs - Summary'!$F$6:$K$7,6),"")
CK3:CK6	CK3	=IFERROR(COS($BO3*PI()/180)*COS($BP3*PI()/180),"")
CL3:CL6	CL3	=IFERROR(COS($BO3*PI()/180)*SIN($BP3*PI()/180),"")
CM3:CM6	CM3	=IFERROR(SIN($BO3*PI()/180),"")
CP3:CP6	CP3	=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:CQ6	CQ3	=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
	BO	BP
1	51.60341483	-2.785559208
2	Latitude (Decimal Degrees)	Longitude (Decimal Degrees)
3	51.559479	-3.030939
4	51.559479	-3.030939
All Completed Runs

Cell Formulas
Range	Formula
BO1:BP1	BO1	=AVERAGE(BO3:BO2002)
BO3:BO4	BO3	=IFERROR(VLOOKUP(C3,'Miscellaneous Data - AP'!$B$3:$E$2002, 4, FALSE),"")
BP3:BP4	BP3	=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
	F	G	H	I	J	K
4	Helper table
5	first run	last run	Home parkrun history	Home	latitude (dec deg)	longitude (dec deg)
6	40642	43820	Newport parkrun	1	51.559479	-3.030939
7	43848	43890	Coed Cefn-pwll-du parkrun	2	51.591855	-3.140954
All Completed Runs - Summary

Cell Formulas
Range	Formula
F6	F6	=MINIFS('All Completed Runs'!E3:E2002,'All Completed Runs'!C3:C2002,H6,'All Completed Runs'!E3:E2002,"<="&DATE(2020,1,17))
G6	G6	=MAXIFS('All Completed Runs'!E3:E2002,'All Completed Runs'!C3:C2002,H6,'All Completed Runs'!E3:E2002,"<="&DATE(2020,1,17))
F7	F7	=MINIFS('All Completed Runs'!E3:E2002,'All Completed Runs'!C3:C2002,H7,'All Completed Runs'!E3:E2002,">="&DATE(2020,1,18))
G7	G7	=MAXIFS('All Completed Runs'!E3:E2002,'All Completed Runs'!C3:C2002,H7,'All Completed Runs'!E3:E2002,">="&DATE(2020,1,18))
J6:J7	J6	=INDEX('Miscellaneous Data - AP'!$B$3:$F$2002,MATCH($H6,'Miscellaneous Data - AP'!$B$3:$B$2002,0),4)
K6:K7	K6	=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:

parkrun - My parkrun Record - WORKING_rev20200422.xlsx
	B	C
37	8,477K	7083 km (note that 157 / 226 = 0 relative to home parkrun)
38	5,268 Miles	4401 mi (note that 157 / 226 = 0 relative to home parkrun)
39	51.603415, -2.785559	51.6255, -2.8117
40	closest (2 km) to Rogiet parkrun, United Kingdom - Wales
41	25 km away
42	16 mi away
All Completed Runs - Summary

Cell Formulas
Range	Formula
C37	C37	=TEXT('All Completed Runs'!CG1,0) & " km (note that " & 'All Completed Runs'!CH1 &" relative to home parkrun)"
C38	C38	=TEXT('All Completed Runs'!CG1*0.621371,0) & " mi (note that " & 'All Completed Runs'!CH1 &" relative to home parkrun)"
C39	C39	=TEXT('All Completed Runs'!CN1,"0.0000") & ", " & TEXT('All Completed Runs'!CO1,"0.0000")
B37	B37	=TEXT(ROUND(SUM(SUMIFS('Miscellaneous Data - AP'!I3:I2003,'Miscellaneous Data - AP'!B3:B2003,'All Completed Runs'!C3:C2002)),0),"#,##0")&"K"
B38	B38	=TEXT(ROUND(SUM(SUMIFS('Miscellaneous Data - AP'!J3:J2003,'Miscellaneous Data - AP'!B3:B2003,'All Completed Runs'!C3:C2002)),0),"#,##0")&" Miles"
B39	B39	=ROUND(AVERAGE('All Completed Runs'!BO3:BO2002),6)&", "&ROUND(AVERAGE('All Completed Runs'!BP3:BP2002),6)
B40	B40	="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))
B41	B41	=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"
B42	B42	=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]

Hi. Please could you give a link to the file?

 

[ollyhughes1982]

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]

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

parkrun - My parkrun Record - WORKING_rev20200422a.xlsx
	B	C
43	60.180235, 24.946594	60.180235, 24.946594
44	Tokoinranta parkrun, Finland	Tokoinranta parkrun, Finland
45	Tokoinranta parkrun, Finland - 1,974K Away	Tokoinranta parkrun, Finland - 1,954 K Away
46	Tokoinranta parkrun, Finland - 1,226 Miles Away	Tokoinranta parkrun, Finland - 1,214 Miles Away
All Completed Runs - Summary

Cell Formulas
Range	Formula
B43	B43	=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))
C43	C43	=INDEX('All Completed Runs'!BQ3:BQ2002,MATCH(AGGREGATE(14,6,'All Completed Runs'!CG3:CG2002,1),'All Completed Runs'!CG3:CG2002,0))
B44	B44	=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))
C44	C44	=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))
B45	B45	=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"
C45	C45	=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"
B46	B46	=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"
C46	C46	=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

Dropbox

www.dropbox.com

 

[ollyhughes1982]

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]

You're welcome...glad to help. Post back if something needs attention.