MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Faster Array Formula?

Posted by JAF on April 03, 2000 8:37 AM

I have an array formula (MAX IF) which returns the maximum value in Column G if 2 specific criteria in columns B and C are met.

The formula itself works fine, but it's extremely slow as it's interrogating a very large file (it took over an hour to calculate on approimately 17,000 records).

I have "tweaked" the formula by sorting the data and incorporating it into a macro which copies down the MAX IF formula 50 rows at a time (each "sort record" appears a maximum of 48 times). Once the 50 have calculated, I copy the range and paste back in as values and then repeat until the all records have been done.

While this is quicker, is there any other way of speeding up an array formula when dealing with large numbers (between 10,000 and 40,000) of records?


Posted by AB on April 04, 2000 12:57 PM

Send me an example, I'd like to see it.