SpeedTools Beta 3 has completed and SpeedTools is now live. You can download the 30-day trial from here, or purchase a license from here.
In part 1 I looked at how FastExcel SpeedTools MEMLOOKUP and AVLOOKUP2 compared to VLOOKUP and INDEX/MATCH for ease of use and power.
This post will benchmark the performance of the SpeedTools lookups against the standard Excel functions.
You can download a free 30-day trial of SpeedTools from the Decision Models website.
The test workbooks are VLOOKUP2.xlsx and MEMLOOKUP2.xlsx in the downloadable file MemLookup2.zip .
A problem with all Excel LOOKUP formulas is that if even if only one of the values in the Lookup Table changes every single LOOKUP formula that refers to the lookup table gets recalculated, although most of them will returns a completely unchanged answer. When you have large amounts of data (tens or hundreds of thousands of rows) this can be very slow.
SpeedTools MEMLOOKUP and AVLOOKUP2 both use a variation of the superfast binary search algorithm that can do exact match searches on sorted data. You can make Excel’s VLOOKUP do a similar thing by using two VLOOKUPS and an IF (see Why 2 VLOOKUPS are better than 1 VLOOKUP).
If you sort the data in the test workbook and use the sorted data option it takes about 0.14 seconds to do 20000 MEMLOOKUPs on 70000 rows on my system. This compares with about 4.25 seconds to do the same thing with VLOOKUP using the VLOOKUP exact match option. (The 2 VLOOKUPS trick is faster than MEMLOOKUP but more complicated!).
But if your data is not sorted you are stuck with doing a slow linear search from the start until a match is found. The VLOOKUP2.xlsx file has 20000 VLOOKUPs on a lookup table with 70000 rows.
This calculates in 4.25 seconds on my desktop system (Intel i7 quad core 870 2.93GHz with 4 GB RAM and using Excel 2013 32-bit and Windows 7). This actually quite fast if you consider that Excel has to make about 1100 million comparisons (so thats 258 MXIPS – Million eXcel Instructions Per Second).
But if you do exactly the same thing (see test workbook MEMLOOKUP2.xlsx) using SpeedTools MEMLOOKUP it only takes 0.12 seconds! Thats about 35 times faster.
So how does it work?
The MEMLOOKUP and AVLOOKUP family of functions are implemented using a multi-threaded C++ XLL. This is the fastest available technology for extending Excel’s function library, and allows the functions to support all the Excel versions from Excel 2013 64-bit to Excel 2000.
MEMLOOKUP and AVLOOKUP2 store in memory the index of the lookup result for each row.
So suppose for the MEMLOOKUP on row 3 the result was found in the 47th row of the lookup table. Then MEMLOOKUP stores in memory 47 for row 3.
At the next recalculation of that formula MEMLOOKUP first looks in the memory, finds 47 and checks if the lookup column row 47 still gives the correct result.
If it does then MEMLOOKUP returns the result from the answer column of row 47 in the lookup table.
If row 47 no longer gives the correct result (because the data in the lookup column on that row in the lookup table has changed) then MEMLOOKUP does a full lookup.
This is a fail-safe and very efficient process.
If (as often happens) you have more than one lookup on the same row returning data from different columns then the lookup memory can be reused for the subsequent lookups. This built-in optimisation is similar to creating an extra MATCH column with several MATCH formulas referring to the MATCH, but is much simpler and more automatic.
Memory is stored with the workbook.
The lookup memory is automatically stored and retrieved with the workbook so that when you reopen a workbook your MEMLOOKUP and AVLOOKUP2 formulas will reuse the lookup memory from the previous calculation.
SpeedTools has options for 4 different kinds of lookup memory:
The SpeedTools MEMLOOKUP and AVLOOKUP family of Lookup functions provide significant performance advantages compared to the standard Excel lookup functions, together with enhanced ease of use and extended capability.
Please try them out and let me know what you think.
Its time for some peformance tests to see how the new functions in SpeedTools stack up against the standard Excel functions. First up is MEMLOOKUP and AVLOOKUP2 vs VLOOKUP!
Most people want their LOOKUPs to tell them when the thing they are looking for does not exist in the lookup table. And most of the time people are working with unsorted data.
Unfortunately VLOOKUP’s default settings don’t do that: it defaults to trying to give you an approximate match on sorted data.
So if you use the VLOOKUP defaults you will probably get the wrong answer!
MEMLOOKUP always does an exact match, even with sorted data (but it will still use fast binary search if you have sorted data and tell MEMLOOKUP about it).
AVLOOKUP2 also defaults to unsorted data and exact match, with an option for approximate match on sorted data if you are sure thats what you want.
Here is an example of VLOOKUP getting the wrong answers when using its defaults:
And here is the same example showing MEMLOOKUP getting the correct results.
AVLOOKUP2 has separate parameters for sorted data and exact match, and can use the superfast binary search algorithm on sorted data for both exact match and approximate match.
Both MEMLOOKUP and AVLOOKUP2 allow you to use column labels from a header row instead of column numbers. This is easier to use and understand, and also makes your LOOKUP formulas more resistant to changes such as rearrangement of the data or extra columns appearing. And you can also use this to do 2-dimensional lookup.
AVLOOKUP2 allows you to specify what you want returned if no exact match can be found, avoiding the need for wrapping the LOOKUP inside an IFERROR function.
You can tell AVLOOKUP2 which column to use for the lookup using a column label or a column number.
AVLOOKUP2 makes it simple to use multiple lookup columns (you can use a constant array {“Jess”,”Ben”} or a range of cells).
AVLOOKUPNTH extends AVLOOKUP2 with an extra parameter so that you can find the first, last or Nth match when you have duplicates, for text, numbers and dates etc.
AVLOOKUPS2 returns ALL the records that match the lookup criteria. You can use AVLOOKUPS2 either as a multi-cell array formula or embedded inside an aggregating function like MAX, SUM, MEDIAN etc.
The SpeedTools Lookup family also includes variations for MATCH as opposed to LOOKUP, Case-Sensitive lookups and lookups using Regular Expressions.
You can download a free 30-day trial of SpeedTools from the Decision Models website.
And you can also download a workbook VLOOKUP1.xlsx in MemLookup2.zip that contains all the examples used above.
Its always a convenient shorthand to say that UDFs and formulas are recalculated when one of their arguments (or a precedent further upstream in the calculation chain) changes.
But in fact that turns out to be a bit of an oversimplification of how Excel works.
I have 2 UDFs in a standard VBA module:
I have used F9 in the VBE to switch to debug mode whenever either of these UDFs execute.
The first UDF (Depends) has 2 arguments (Arg1 and Arg2), but only the first of them (Arg1) is actually used by the UDF. The second UDF (Depends2) uses both the arguments.
The Excel sheet has 2 sets of data for Arg1 and Arg2 and then calls both the UDFs. Calculation is set to Automatic.
The result of Depends is 6, and of Depends2 is 24.
When you press F9 nothing happens because nothing has changed to cause a recalculation.
I call the Arg2 dependency in the first UDF (Depends) a False Dependency since its not actually needed.
Things work differently if you make one of the dependencies volatile. Lets change cell B2 to =RAND()*100
As expected both UDFs recalculate.
Now press F9 to recalculate again without changing anything else.
Depends2 recalculates, but Depends does NOT recalculate even though a value in Arg2 of the Depends UDF has changed.
In other words if the False Dependency is Volatile it is ignored in a recalculation.
This also happens with built-in Excel functions like INDEX().
If A1 contains =NOW(), and A2:A5 contain the numbers 2 to 5 then
I call Volatile False Dependencies Indirect Dependencies.
Stephen Gersuk discovered what looks like another bug with the EVALUATE method and volatile dependencies.
If you have a UDF like this:
Function MySum2(r As Range) As Double
MySum2 = Evaluate("sum(" & r.Address(External:=True) & ")")
End Function
then it does not get recalculated when it has a volatile precedent and you press F9.
So this case gives you the wrong answer, because its not a true False Volatile Dependency: the result really does depend on the argument.
You can bypass this bug by adding anything that references the Value of a cell in the argument:
Function MySum2(r As Range) As Double
If IsEmpty(r) Then Exit Function
MySum2 = Evaluate("sum(" & r.Address(External:=True) & ")")
End Function
Function MySum3(r As Range) As Double
Dim strAdd As String
strAdd = r.Address(External:=True)
MySum3 = Evaluate("sum(" & strAdd & ")")
End Function
It has been suggested that you can use False Dependencies to control the sequence in which Excel calculates formulas.
This is a dangerous idea because false dependencies on uncalculated cells cannot be recognised by Excel since it does not get a chance to discover that they are uncalculated.
Do you have any bad experiences with volatile functions?
The previous post discussed how Excel’s data types, and how FILTER.IFS was designed to handle them.
Colin Legg suggested that a good starting point for the design choices could be what COUNTIF/SUMIF do. So here is an example of some of the problems with COUNTIF, and what the equivalent SpeedTools function ACOUNTIFS does. (ACOUNTIFS uses the same filtering engine as FILTER.IFS).
Suppose you have a list of zero-prefixed numbers, headed DATA, and you want to count how many of each of the numbers there are:
Each zero-prefixed number is unique apart from 0012345 which appears twice in the second and third row (54 and 55).
So I created a COUNTIF formula to count the number of occurrences in the list for each number, using each of the different criteria operators.
If COUNTIF works correctly in this situation the answer should be {1;2;2;1;1}, but as you can see below it gets it wrong!
Each row in the table tries to count how many of the corresponding cell can be found using the relational operator.
So COUNTIF always give 5 when using =, so I think it must be converting ALL the text in both the data and the criteria to numbers.
And < and > always give zero because COUNTIF thinks all the data cells contain the same thing (a number 12345).
But COUNTIF($A$50:$A$54,”<>” & $A50) also gives 5 ! This looks like a BUG to me.
Here is what the SpeedTools ACOUNTIFS function gives:
ACOUNTIFS treats the text numbers as text numbers and so gives what looks to me like a more “correct” answer for all the relational operators.
Using COUNTIF/SUMIF/COUNTIFS/SUMIFS with mixed data types looks very unwise to me!
But maybe you can figure out a way to make them work sensibly?
Excel has only 4 or 5 native data types:
You can format all these data types in lots of different ways so that they look different, but a Cell’s underlying value is always going to be one of these types.
And unlike most Database systems Excel allows the cells in a column to contain multiple data types.
This can lead to problems: the most frequent one being a column of numbers some of which have been entered as text strings and some as real numbers. Usually you can visually see them because the numbers that are text are left-aligned in the cell and the real numbers are right-aligned.
Numbers as text can arrive in Excel in various ways:
When Excel sorts data contining different data types it uses this relationship between types:
Numbers<Strings<Booleans<Errors
Empty cells are always sorted last, both in Ascending and Descending sorts!
When you sort data containing numbers stored as text strings Excel asks you if you want to sort Text numbers as text or as numbers. Usually its better to sort text numbers as text rather than risk confusing any subsequent operation that relies on things being properly sorted.
If you use a simple formula (=A6<A5) to compare data types you get this:
You can see that XYZ is >= the empty cell above it, but ABCD is <XYZ.
Numeric 1234 in A9 is less than text string 1234 in A8
A12 entered as a ‘ is a zero-length string and is > the number in A11.
The errors in A16:A18 propagate so you can’t see how Excel compares them.
So if you get rid of the error cells, sort the data and change the formula so its looking for A6>A5 you get this:
So the formula comparison precedence rules are the same as the Excel sorting rules, except for empty cells!
Its useful to be able to filter by data type (although the standard Excel Filter command does not have this option), so I added some type filtering operators:
You can prefix these operators with ¬ to filter out everything that does NOT match the data type.
And you can have a list of multiple filtering operators: {“¬”,”#EMPTY”,”#ZLS”,”#BLANK”,”#ERR”} would exclude empty cells, cells with zero length strings or blanks, and cells with errors.
Here is an example:
The FILTER.IFS formula is =FILTER.IFS(0,$A$6:$B$17,1,D$5) entered as an array formula (Control-Shift-Enter) and copied across.
Suppose you create a FILTER.IFS formula like this: =FILTER.IFS(1,$A$6:$B$17,2,1,”<1235″)
The criterion says less than 1235, but which 1235 – the numeric one or the string one or both?
I don’t think there is neccessarily a “correct” answer to this, so I invented a rule!
If the Criterion value can be converted into more than one data type (in this case a string and a number) choose the same data type as the first cell in the column.
In this case the first cell is a number, so FILTER.IFS chose to use numeric 1235, which results in a single result, the 1 from row 6.
Because the data is sorted the binary search routine has to use a single datatype, so looking for both the string 1235 and the numeric 1235 is not an option.
But if the data is NOT sorted a linear search can find both: so you can tell FILTER.IFS to compare using ALL the avialable datatypes by using an & prefix.
=FILTER.IFS(0,$A$6:$B$17,2,1,”&<1235″)
you get ALL the data which is less than numeric 1235 AND all the data that is less than string 1235.
Of course if you don’t use any of the Criterion operators and it finds only the matching data type (=FILTER.IFS(1,$A$6:$B$17,2,1,”1235″) or =FILTER.IFS(1,$A$6:$B$17,2,1,1235)
Using mixed data types with relational operators can be tricky – sometimes its difficult to work out what Excel is doing.
A drawback of following the same kind of syntax as SUMIFS (a string containing both the relational operator and the value) is that there is no clear datatype choice.
But I was not sure that the previous FILTER.IFS design, which could give different results for sorted and unsorted data, made sense, so I changed it so that sorted and unsorted data gave the same results and added the & prefix :
What do you think?
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 solutions in some instances, and the introduction of SUMIFS in Excel 2007 gave a fast alternative for some scenarios.
But there is still a need for a powerful, dynamic function that can perform better than SUMPRODUCT/ array formulas, so let me introduce my attempt at creating one : FILTERIFS.
The original implementation was done using a VB6 automation addin, but lack of multi-threading and 64-bit support in VB6 lead me to re-implement as a C++ XLL.
The idea is to process each criterion in turn using only the rows that meet all the criteria processed so far, thus avoiding the SUMPRODUCT/array formula approach of evaluating all the criteria for all the rows.
Criteria operating on sorted columns are processed first using a fast High-Low binary search modified for relational operators.
Non-sorted columns and criterion types like Regex are then processed using linear search in a sequence designed to minimise data transfer/coercion time.
And using a C++ XLL allows multi-threading and fast execution.
The syntax uses a similar approach to SUMIFS to pass the criterion as a string concatenation of a relational operator and a value. Because the value is passed as a string FILTERIFS has to do some datatype conversions of the value to match the datatype of the criterion column (and hopefully avoid some of the SUMIFS bugs in this area).
FILTERIFS( nSortedCols, InputRange, ReturnCol, CriteriaColumn1, Criteria1,
CriteriaColumn2, Criteria2, … , ["#OR#", nsortedCols,] CriteriaColumnx, Criteriax, …)
nSortedCols gives the number of columns which are sorted in the InputRange
InputRange is a range reference to the data containing the sorted columns and return column.
The data can contain a header row of names for the columns.
ReturnCol is the header name or number of the column within InputRange to return results from.
Criteria Column gives either the name/number of a column in InputRange, or a range reference to an independent column, or an array or an expression returning a column of data to be used as the criterion column.
Criteria is the expression used to filter the criterion column.
This can be a relational operator (=, >=, <=, >, <, ¬=, ~, ¬~, ~~) ( ¬ means NOT, ~ means LIKE, and ~~ means Regex) and value.
It can also be a LIST of alternatives to look for, given either as an array ({“FL”,”NY”,TX”} or with a relational operator {“~ABC*”,”~DEF*}) or as a range reference.
#OR# allows you to have multiple alternative sets of criteria.
To deliver this fairly complex set of capabilities the function is broken down into a number of component blocks. These are the major ones:
These components currently result in just under 5000 lines of code.
FILTERIFS Status
As at December 2012 the function is coded and the first phase of testing has been completed. It has taken considerably longer than planned, mainly because the VB6 version made extensive use of EVALUATE, which turned out not to be allowed to multi-thread in C++ and so I had to redesign most of the approach for non-sorted columns.
There is still some performance testing, refactoring and rework to be done but the target is to start Beta3 in early January 2013.
(Assuming that the Mayan calendar is wrong in predicting the end of the world today Friday 21 Decembery 2012).
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 well: here is the code for the demo function GetUsedRows3:
Option Explicit
'
' create module level array for cache
'
Dim UsedRows(1 To 1000, 1 To 2) As Variant
Public Function GetUsedRows3(theRng As Range)
' store & retrieve used range rows if Excel 2007 & later
Dim strBookSheet As String
Dim j As Long
Dim nFilled As Long
Dim nRows As Long
' create label for this workbook & sheet
strBookSheet = Application.Caller.Parent.Parent.Name & "_" & Application.Caller.Parent.Name
If Val(Application.Version) >= 12 Then
' look in cache
For j = LBound(UsedRows) To UBound(UsedRows)
If Len(UsedRows(j, 1)) > 0 Then
nFilled = nFilled + 1
If UsedRows(j, 1) = strBookSheet Then
' found
GetUsedRows3 = UsedRows(j, 2)
Exit Function
End If
Else
' exit loop at first empty row
Exit For
End If
Next j
End If
' find used rows
nRows = theRng.Parent.UsedRange.Rows.Count
'
If Val(Application.Version) >= 12 Then
' store in cache
nFilled = nFilled + 1
If nFilled <= UBound(UsedRows) Then
UsedRows(nFilled, 1) = strBookSheet
UsedRows(nFilled, 2) = nRows
End If
End If
'
GetUsedRows3 = nRows
End Function
Sub ClearCache()
'
' empty the first row of the used-range cache
'
UsedRows(1, 1) = ""
End Sub
Note: there is no error handling in this code!
Start by defining a module level array (UsedRows) with 1000 rows and 2 columns. Each row will hold a key in column 1 (book name and sheet name) and the number of rows in the used range for that sheet in that book in column 2. I have assumed that we will only cache the first 1000 worksheets containing these UDFs!
The key or label is created by concatenating the name of the parent of the calling cell (which is the worksheet) to the name of the parent of the parent of the calling cell (which is the workbook containing the sheet).
Then loop down the UsedRows array looking for the key, but exit the loop at the first empty row.
If the key is found, retrieve the number of rows in the used range from column 2, return it as the result of the function and exit the function.
Otherwise find the number of rows in the used range, store it in the next row of the UsedRange cache and return it as the result of the function.
You can see that the function only operates the cache for Excel 2007 and later versions. There are two reasons for this:
We need to empty the cache after each calculate because the user might alter the used range and so the safe thing to do is to recreate the cache at each calculation.
AfterCalculate is an Application Level event which is triggered after completion of a a calculation and associated queries and refreshes. (A BeforeCalculate event would be even more useful but does not exist!)
Chip Pearson has an excellent page on Application Events. I always consult it when I need application events because I can never remember exactly how to do it!
First I added a Class Module called AppEvents with code like this:
Option Explicit
Private WithEvents App As Application
Private Sub Class_Initialize()
Set App = Application
End Sub
Private Sub App_AfterCalculate()
ClearCache
End Sub
Then I added some code to the ThisWorkbook module:
Option Explicit
Private XLAppEvents As AppEvents
Private Sub Workbook_Open()
Set XLAppEvents = New AppEvents
End Sub
This sets up the hooks that are needed for Application level events. Quite a lot of code just to run the ClearCache sub after each calculation!
ClearCache just empties the first key in the Cache so that the find loop in GetUsedRows3 exits straight away.
This code is ignored in Excel 2003 and earlier: since the AfterCalculate event does not exist it never gets called but still compiles OK.
For 640K rows of data 1000 calls to GetUsedRows3 takes 66 milliseconds. The original CountUsedRows function took 33 seconds.
Thats a speedup factor of 500!
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 later someone will try to use it with a full-column reference:
=MyUDF(A:A,42)
When Excel 2007 introduced the “Big Grid” with just over 1 million rows it became even more important to handle these full-column references efficiently. The standard way to handle this in a VBA UDF is to get the INTERSECT of the full-column reference and the used-range so that the UDF only has to process the part of the full-column that has actually been used. The example VBA code below does this intersection and then returns the smaller of the number of rows in the input range and the number of rows in the used range.
Public Function GetUsedRows(theRng As Range)
Dim oRng As Range
Set oRng = Intersect(theRng, theRng.Parent.UsedRange)
GetUsedRows = oRng.Rows.Count
End Function
The parent of theRng is the worksheet that contains it, so theRng.Parent.UsedRange gets the used range of the worksheet you want.
Two problems with this technique are:
I created a very simple UDF and timed the calculation of 1000 calls to this UDF for filled used ranges of between 10K rows and 640K rows.
Public Function CountUsedRows()
CountUsedRows = ActiveSheet.UsedRange.Rows.Count
End Function
It turns out that the time taken to execute this UDF is a linear function of the number of used rows in the used range.
And its quite slow, 1000 calls to this UDF with 640K rows of data takes 33 seconds!
When the used range is small you won’t notice the time taken, but for large used ranges with the big grid you certainly will. And the problem is that your UDF will do this check on every range that is passed to the UDF, even if its not really needed.
Colin points out that what affects the time is actually the number of cells containing data or formatting (or that previously contained data or formatting) rather than the last cell in the used range.
So you could start by only doing the used-range check when theRng parameter has a large number of rows:
Public Function GetUsedRows2(theRng As Range)
Dim oRng As Range
If theRng.Rows.Count > 500000 Then
Set oRng = Intersect(theRng, theRng.Parent.UsedRange)
GetUsedRows = oRng.Rows.Count
Else
GetUsedRows = theRng.Rows.Count
End If
End Function
This example only does the check if the user gives the UDF a range referring to more than half a million rows.
Another, more complicated, way of minimising the time is to store the number of rows in the used range in a cache somewhere and retrieve it from the cache when needed. The tricky part of this is to make sure that the used-range row cache always is either empty (in which case go and get the number) or contains an up-to-date number.
One way of doing this would be to use the Application AfterCalculate event (which was introduced in Excel 2007) to empty the cache. Then only the first UDF that requested the used range for each worksheet would use time to find the used range, and (assuming that the calculation itself did nothing to alter the used range) the correct number would always be retrieved.
The equivalent for Excel versions before Excel 2007 would be to use the Application SheetCalculate event to empty the cache for that particular worksheet. This technique would be less efficient since a worsheet may well be calculated several times in each calculation cycle.
As Colin points out, if you want to find the last row containing data it is faster to use Range.Find when you have many cells containing data.
Note that you can only use Range.Find in UDFS in Excel 2002 and later, and you cannot use the Find method at all from an XLL except in a command macro or via COM.
Public Function CountUsedRows2()
CountUsedRows2 = ActiveSheet.Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End Function
I have just about finished converting the first part of the FILTER.IFS function from VB6 to C++.
This first part uses modified binary search routines to handle multiple kinds of criteria (EQ, GT, LT, GE, LE, NE with AND and OR etc) on sorted columns.
The second part handles unsorted columns and additional criteria types such as Regex and Like. Some of this is done in the VB6 version of the function using EVALUATE to get the results of array formulas on subsets of the data. So the C++ XLL version uses the same technique but using the XLL equivalent xlfEvaluate.
The explanation here http://msdn.microsoft.com/en-us/library/office/bb687899.aspx seems to say that using xlfEvaluate is threadsafe as long as the expression being evaluated does not contain any non-threadsafe components.
But in practice using xlfEvaluate on even a simple formula string like =2+3 fails with a return code of 128 if the UDF function is flagged as multi-threaded, but works OK if the function is flagged as single-threaded.
At the moment this leaves me with a choice of either making FILTER.IFS single-threaded or using some alternative to xlfEvaluate. Both of these choices look bad.
