Perf issue: Conditional avgs on huge dataset

[Mc922]

Hello all,

I'm running into an issue with a very large dataset. Let's say on Sheet1 I have close to 70k rows each with a unique customer name sorted by name ascending, next to that in column B I want to calculate an average sale for each customer. On Sheet2 I have again the customer in column A sorted acsending, and in column B I have the amount of each sales transaction. Sheet2 has approx 500,000 rows. On Sheet1 if I use AverageIF for this conditional average by customer name on Sheet2, we're talking about something like 15-20 mins to populate the column as soon as I fill-down the Quad core CPU is pegged. What are my options to get the fastest possible calculation time? I know Access can do this using a group by / totals query in probably under 20 seconds, and Excel using a pivot table is probably comparable. Doing this in Access brings other issues that I'd rather avoid and the pivot table, well I'd like to consider other possible options first as ultimately I need to act upon the results of these averages using iterative calculation... it's kind of a complex problem but suggestions are appreciated. -Mike

 

[shg]

I don't know how this would compare performance wise ...

Select any cell on sheet1 row 2.

Assuming sheet2 col B contains sale amounts, define a range

=INDEX(Sheet2!$B:$B, MATCH(Sheet1!$A2, Sheet2!$A:$A, 0)):INDEX(Sheet2!$B:$B, MATCH(Sheet1!$A2, Sheet2!$A:$A))

(The second index function is fast (the possible saving grace), the first is slow.)

Then in col B on Sheet1, =AVERAGE(ThisCust)