Part 10: Summary

In this series, we have undertaken a variety of tests to identify what will provide us with the fastest calculating formulas. Now it is time to bring all of those learnings together. In this final part, we will try to focus on implementing the methods which will give us the greatest benefit.

Single criteria

Where there is a single criterion the following will help to reduce calculation times:

Sort data only where the results looked up are weighted towards the records earlier in the source data set. Do not sort data unless you have tested that it saves calculation time, as it is also possible for sorting data to slow down a calculation. We need to consider this on a case by case basis (Part 5 – Sorting source data). (This assumes we are not using an approximate match method).

Summarized data with unique records calculate faster than source data which requires a calculation (SUMIF for example) to be performed. Therefore, if it is possible to summarize the data to the right levels first it can significantly reduce calculation time. (Part 4 – Lookup formulas compared with Part 8 – Sum with multiple criteria)

Multiple criteria

Where there are multiple criteria the following will reduce calculation times:

Arrange source data in a table layout, rather than a list. Use the VLOOKUP/MATCH or INDEX/MATCH/MATCH formulas. We may need to use helper columns and helper rows to create the table. (Part 6 – Table or list)