Monthly Archives: December 2012

The SpeedTools FILTERIFS function: Design and Implementation Part 1

Posted on December 21, 2012 by fastexcel

Excel users have been using SUMPRODUCT and array formulas to create multiple-condition formulas for many years. This is a powerful technique, but can be painfully slow with large amounts of data. Pivot Tables and Excel 2013’s PowerPivot can provide good … Continue reading →

Posted in Calculation, Lookups, UDF, VBA, XLL | 2 Comments

Pinot Noir Tasting: Old World 95 to 2004 versus New World 2006 to 2008

Posted on December 8, 2012 by fastexcel

Some of you will know that I am a bit of a fanatic about Pinot Noir. Once you get hooked by the (often expensive) wine made from this grape everything else seems slightly second-best. Once or twice a year a … Continue reading →

Posted in Wine | 1 Comment

Writing Efficient UDFs Part 12: Getting Used Range Fast using Application Events and a Cache

Posted on December 5, 2012 by fastexcel

In the previous post I suggested that one good way to speed up retrieval of the Used Range last row would be to use a Cache and the AfterCalculate Application event. I have now tested this approach and it works … Continue reading →

Posted in Calculation, UDF, Uncategorized, VBA | 2 Comments

Writing Efficient UDFs Part 11 – Full-Column References in UDFs: Used Range is Slow

Posted on December 2, 2012 by fastexcel

Excel users often find it convenient to use full-column references in formulas to avoid having to adjust the formulas every time new data is added. So when you write a User Defined Function (UDF) you can expect that sooner or … Continue reading →

Posted in Calculation, UDF, VBA, XLL | 17 Comments