Speed up a 100k AverageIf formulas

Hi,

I have this file in which I want to use the averageifs formula to process some raw data (sheets 1 and 2).

The thing is, with over 100k calculations to do, it takes literally AGES to process all the data (somewhere between 1h-2h), and I need a way to speed things up, maybe through VBA. Any ideas on how to accomplish this? I read somewhere that using arrays in VBA is a much quicker process than using only Excel, but I don't know if this is the case.

Can you help?

Thanks

PS: I've attached a sample file with 200 rows, in which I created a macro to count the time it takes to make all those calculations. On my PC it takes little over than 2 seconds. The problem is that having 9000 rows doesn't take 2 * 9000 / 200 seconds...

The first quick-win in these scenarios is to remove the requirement for the multi conditional test in the first instance.

Using your sample file - if you concatenate the fields of interest to a "key" on Sheet 1 then you can revert to AVERAGEIF using a single test based on the key.

Please Login or Register  to view this content.

You can then revert your formulae to:

Please Login or Register  to view this content.

In terms of performance - on my machine your original matrix took around 4.5 seconds to execute whereas utilising the above the calc time drops to around 0.6 seconds.

If the performance gains remaining insufficient then the next thing to do would be to look into sorting your data on Sheet 1 (appropriately).
Sorting permits use of Binary Search based calculations which on large sets invariably offer some efficiencies and also has other advantages too regards helper calcs to reduce burden.

With the data sorted you can dispense with the need for the AVERAGE if altogether - if you're prepared to use additional helper calcs.

It's often the case that a greater number of lightweight calcs will perform far better than fewer more elegant yet expensive formulae.

Illustrating via your latest sample

Please Login or Register  to view this content.

what this does is essentially conduct the average on the sheet itself but only looking at a small subset of the data (once per Joint@OutputCase combination) and using binary search to identify the ranges... this means these calculations are incredibly fast.

With the above in place all you need do now is modify the formula on your summary sheet to utilise these values and again use Binary Search based Lookup to retrieve the appropriate value.

Please Login or Register  to view this content.

The above performs two binary search based lookups - the first retrieves the last average value listed where the key code is <= $A4&"@"&$F$2.
It follows that given LOOKUP is approximate match test the resulting value may or may not be for this specific key so we multiply the result by whether or not the key matches our criteria (0 result if not).

It's very important to note that this type of LOOKUP is blindingly fast so even though we're conducting two LOOKUPs they will be significantly quicker than the single AVERAGEIF calc being performed previously.

With the above approach implemented in your file the calculation time for the matrix dropped from 0.6 seconds to between 0.02 and 0.04 seconds ... ie very very fast and a big % gain.

The key is all calculations in place are now very light weight and as such will perform very efficiently - even though there are more of them.

Great news.

There are a few examples of these types of gains on this board and in general all follow the same pattern that is to say:

-- sorting data (allowing for binary search methods)
-- use of basic & efficient helpers
-- avoiding multi conditional summary calculations en masse or in conjunction with large data sets (avoiding Volatility)

Charles Williams' site is excellent resource for all things calculation if you're interested in optimisation etc:

http://www.decisionmodels.com/optspeed.htm
http://www.decisionmodels.com/calcsecrets.htm