Part 1: Introduction – Part 2: Timer – Part 3: Volatile formulas – Part 4: Lookup formulas – Part 5: Sorting source data – Part 6: Table or List? – Part 7: Multiple criteria lookups – Part 8: Sum with multiple criteria – Part 9: Other factors – Part 10: Summary
Part 9: Other factors
So far in this series we have undertaken a number of tests to find out which formulas are the fastest and how best to layout the source data. But, in this part we will be considering a number of other factors, and how they impact calculation speed.
- Does the location of the source data impact calculation speed?
- Does referencing the whole column in a calculation range, rather than the specific range, impact calculation speed?
A lot of sites list these as considerations when trying to optimize calculation speed. But how much impact do they really have?
If you want to use the workbook and find out for yourself you can download it here.
Location of source data
In relation to our formulas there are 3 places we can keep our source data:
- Within the same worksheet
- Within a different worksheet of the same workbook
- Within a worksheet of a separate workbook
The general advice to optimize calculation speed is to keep the data within the same worksheet as the formulas. Or, at the very least ensure it is in the same workbook.
The results
Look at the results above, the times are all very similar. Based on this test it actually makes very little difference where the data is stored. The slowest calculation occurs when the data is a different workbook. However the impact is only 2%, so probably not a key focus area when trying to speed up calculations.
Referencing the whole column, rather than the specific cells
A number of formulas can be written so that they reference specific cells, or whole columns. SUMIF is probably the best example of this type of formula. Other articles state that calculation speed is impacted when referencing more cells than required.
The results
There is a difference in the times between the two scenarios – approximately 4% difference. So, whilst there is a speed boost, it is not very significant.
Conclusion
Whilst it is correct that data source location and having a specified range of a formula can have an impact on speed, the speed impact is only 2% – 4%. If you are experiencing slow calculating formulas, making changes to your worksheets for these is unlikely to solve the issue. However, when setting up a workbook it would be useful to plan so that these are not an issue.
A little accidental discovery – how does Battery Power or Mains Power impact speed?
Whilst creating this series, I noticed I was getting different calculation speeds depending on whether my laptop was running on battery power or mains power. I had not really considered this to be an issue before. But I thought I would test the impact.
As you can see from the table above, there is approx 14% speed increase from having a laptop plugged in. Is this specific to my laptop? Possibly. I don’t know. But it is worth testing, if you do have slow calculating workbooks.
Next time
In the final part we will be bringing together all the learnings from this series. We compare which formulas or methods really create the biggest speed difference.
