Workbook efficiency and lag

[ollyhughes1982]

Hi,

I have a large spreadsheet, containing all of my parkrun data and challenges. Over the past year it has become quite bloated and is currently around 85mb and is prone to laggy behaviour. Now that I have largely finished developing the spreadsheet (I will add ad hoc extra sheets etc. as I go along here and there, but no more major additions), I am looking to go back through the workbook and attempt to reduce the lag and increase efficiency of formulas. I think one such example might be the one below. I have the formula in D3:D10002, which identifies the relevant country in which a parkrun takes place, according to the parkrun country code (C3:C10002):

=IF(C3>=1,(IF(C3=3,"Australia",(IF(C3=4,"Austria",(IF(C3=14,"Canada",(IF(C3=23,"Denmark",(IF(C3=30,"Finland",(IF(C3=31,"France",(IF(C3=32,"Germany",(IF(C3="Iceland","Iceland",(IF(C3=42,"Ireland",(IF(C3=44,"Italy",(IF(C3=46,"Japan",(IF(C3=57,"Malaysia",(IF(C3=64,"Netherlands",(IF(C3=65,"New Zealand",(IF(C3=67,"Norway",(IF(C3=74,"Poland",(IF(C3=79,"Russia",(IF(C3=82,"Singapore",(IF(AND(C3=85,Q3<>"eSwatini",Q3<>"Namibia"),"South Africa",(IF(C3=88,"Sweden",(IF(AND(C3=97,Q3<>"Crown Dependencies",Q3<>"Channel Islands",Q3<>"Falkland Islands"),"United Kingdom",(IF(C3=98,"USA",(IF(C3="Zimbabwe","Zimbabwe",(IF(Q3="eSwatini","Eswatini",(IF(Q3="Namibia","Namibia",(IF(OR(Q3="Crown Dependencies",Q3="Channel Islands",Q3="Falkland Islands"),"UK Crown Dependencies & UK Overseas Territories",(IF(C3="","",)))))))))))))))))))))))))))))))))))))))))))))))))))))),"")

I have also been going back through and converting INDEX/MATCH formulas to XLOOKUP, which seems to be reducing the file size - this is on-going. Does anyone else find XLOOKUP redcuce file size / lag compared with INDEX/MATCH and VLOOKUP?

I think these three main sheets may be the main culprits:
- parkrun Reader Dump
- Miscellaneous Data - P & C p
- Additional Loc. Data - Look-Up

In particular I think the location calculations could be quite laggy? Below is one example, where distance between co-ordinates is calculated:

=IFERROR(IF(C3<>"",(6371*(ACOS(SIN(INDEX($E$3:$H$10002,MATCH('All Completed Runs - Summary'!$A$11,$H$3:$H$10002,0),1)*PI()/180)*SIN(E3*PI()/180)+COS(INDEX($E$3:$H$10002,MATCH('All Completed Runs - Summary'!$A$11,$H$3:$H$10002,0),1)*PI()/180)*COS(E3*PI()/180)*COS((INDEX($E$3:$H$10002,MATCH('All Completed Runs - Summary'!$A$11,$H$3:$H$10002,0),2)-F3)*PI()/180)))),""),"")

I have been through the workbook and already reduced conditional formatting to as little and as most efficient a state as possible - as I have previously read that lots of conditional formatting could cause problems.

I have included a link to file, if there's anyone who might be able to suggest any other improvements I might make.

Link: parkrun - Inefficient Formulas.xlsx

Thanks in advance,

Olly.

PS. I haven't been able to find anything, but is there any tool in Excel where you can see which formulas etc. might be taking the most calculation time and causing the lag? Maybe a plug-in is needed?

 

[offthelip]

One way you could speed up the calculation of the distance from the coordinates is to create two helper column on the "'All Completed Runs - Summary'" worksheet which contain the Sin and the Cosine of the coordinates, then in your equation above you just reference the sin and cos values rather than the raw coordinates. You can include the PI()/180 too

 

[ollyhughes1982]

One way you could speed up the calculation of the distance from the coordinates is to create two helper column on the "'All Completed Runs - Summary'" worksheet which contain the Sin and the Cosine of the coordinates, then in your equation above you just reference the sin and cos values rather than the raw coordinates. You can include the PI()/180 too

Thanks