Peter wants to calculate a rolling average over 600K rows. His data consists of a Timestamp in Column A and a Value in column B:
| 1340816430| .02 |
He is using an array formula in each of the 600K rows (although it would work as an ordinary non-array formula):
This calculates the average of the values starting at -1000 timestamp units and ending at +1000 timestamp units.
I setup some test data for the full 1048576 rows and found that 500 of the AVERAGEIFS formulas took 25 seconds to calculate on multithreaded 4 core system.
How many MXIPS can AVERAGEIFS do?
AVERAGEIFS is a fast function – its calculating 500 x (2000000 comparisons and averages of matching results) – say 1000 million operations in 25 seconds = 40 MXIPS (Million eXcel Instructions Per Second). The only problem is that Peter wants to do 600K of these, not 500, and it looks like that will take over 8 hours!
A more complicated but faster solution
Lets assume that the data is sorted ascending on Timestamp (anyway Excel is very fast at sorting data). Start by making sure that Excel is in Manual calculation mode!
Then in column C put
and copy down. This finds the row that is 1000 timestamp units before the current row. The MATCH function is using its binary search of sorted data algorithm, and thats lightning fast. The IFERROR is to handle the starting condition for the first rows where there are no Timestamps at -1000 from the current Timestamp.
In Column D put
and copy down. This finds the row that is 1000 timestamp units after the current row, in a similar way to column C
Now we know that start and end row numbers of the block of rows that Peter wants to average. So we can use OFFSET to get just that subset of rows, and feed it to AVERAGE.
So in Column E put
and copy down.
Now press Ctrl/Alt/F9 for a full calculation – on my system that takes just 20 seconds – several thousand times faster than AVERAGEIFS!