Calculation Efficiency

[mrhstn]

Hi all

I'm looking for a little bit of advice on calculation efficiency.

I have a workbook with around 500,000 formulas all of which are quite cumbersome and it takes in the order of 30 minutes to calculate. I don't think it should be taking as long so I'm wondering where I have gone wrong.

An example formula might be something similar to:

=IFERROR(SUMIFS(tbl_Metrics[Value], tbl_Metrics[Metric], $A$3, tbl_Metrics[Station], $B4, tbl_Metrics[Week], D$1, tbl_Metrics[Year], D$2)/COUNTIFS(tbl_Metrics[Metric], $A$3, tbl_Metrics[Station], $B4, tbl_Metrics[Week], D$1, tbl_Metrics[Year], D$2), "-")

I do have some where I've got a few array constants so I've wrapped them in a SUMPRODUCT.

=IFERROR(SUMPRODUCT(SUMIFS(tbl_Metrics[Value], tbl_Metrics[Metric], {"GB*","FR*"}, tbl_Metrics[Station], $B4, tbl_Metrics[Week], D$1, tbl_Metrics[Year], D$2))/SUMPRODUCT(COUNTIFS(tbl_Metrics[Metric], {"GB*","FR*"}, tbl_Metrics[Station], $B4, tbl_Metrics[Week], D$1, tbl_Metrics[Year], D$2)), "-")

tbl_Metrics is near enough 500,000 rows in itself


I've tried splitting the formulas down into helper columns to see if that helped, there was no time saving. I've read over FastExcels article on improving calculation efficiency, which normally helps, but this time I'm a bit stumped.

I'm doing this at work using Excel 2016 (64bit).

Does anyone have any suggestions to help improve performance on this?

TIA

 

[sadboy309]

You need Computer stronger, Chips stronger, RAM stronger. Or, you must use code VBA change by function top.