Makeing the most of your XIPS Part2 – when 40 MXIPS for AVERAGEIFS is too slow

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:

+-Timestamp-+-value-+
| 1340816430|  .02  |
---------------------

He is using an array formula in each of the 600K rows (although it would work as an ordinary non-array formula):

{=AVERAGEIFS(B:B,A:A,"<"&A1+1000,A:A,">"&A1-1000)}
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

=IFERROR(MATCH(A1-1000,$A:$A,1),1)

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

=IFERROR(MATCH(A1+1000,$A:$A,1),1048576)

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

=AVERAGE(OFFSET(B1,C1-ROW(),0,D1-C1+1,1))

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!

Advertisements
This entry was posted in Calculation. Bookmark the permalink.

One Response to Makeing the most of your XIPS Part2 – when 40 MXIPS for AVERAGEIFS is too slow

  1. Peter says:

    I’ll linkback to this post in my final right up so that you can see the impact of your help. Bravo and thanks again!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s