Category Archives: XLL

Inserting a UDF and Launching the Function Wizard from VBA

In a previous post I suggested you could do this by entering the function using VBA into the selected cell using a comma as the argument to the function, for example =LISTDISTINCTS.SUM(,) , and then calling the function wizard using … Continue reading

Posted in UDF, VBA, XLL | Leave a comment

Finding out if a function is Volatile or Multithreaded using VBA: UDFs for UDFs

Part of my new Profiling Formulas and Functions command requires the code to determine whether a Function is  a native built-in Excel function, or an XLL function, or some other kind (VBA, Automation). And I also want to know if … Continue reading

Posted in Calculation, UDF, VBA, XLL | Leave a comment

UNIQUES and DISTINCTS: exploring lists with LISTDISTINCTS

I just added some options to the SpeedTools LISTDISTINCTS functions that make them surprisingly powerful. You can now easily find the most frequently occurring item in a list, or find the item with the largest sum or average of a … Continue reading

Posted in Calculation, UDF, XLL | Tagged , , | Leave a comment

Finding missing items in lists: VLOOKUP vs COMPARE.LISTS performance and ease of use

Returning to the subject of finding the missmatches between 2 lists I want to compare using VLOOKUP with using SpeedTools COMPARE.LISTS. Test Data My test data consists of 2 lists of 500000 6-digit numbers. Most of these numbers match, but … Continue reading

Posted in Calculation, Lookups, UDF, XLL | Leave a comment

SpeedTools AVLOOKUP2 & MEMLOOKUP versus VLOOKUP – Performance, Power and Ease-of-Use Shootout Part 2

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. Download the … Continue reading

Posted in Calculation, Lookups, UDF, XLL | Tagged , , , | 3 Comments

SpeedTools AVLOOKUP2 & MEMLOOKUP versus VLOOKUP – Performance, Power and Ease-of-Use Shootout Part 1

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! SpeedTools Lookups are easier to use, more powerful and less error … Continue reading

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

SpeedTools Beta 3 – Win a SpeedTools Coffee Mug and SpeedTools License

FastExcel SpeedTools Beta 3 FastExcel SpeedTools Beta 3 is a state-of-the-art set of tools to help you speed up calculation of slow Excel workbooks. Download the 30-day trial of FastExcel SpeedTools Beta 3 Download the SpeedToolsHelp file. (you may need … Continue reading

Posted in UDF, VBA, XLL | 19 Comments

The SpeedTools FILTER.IFS Function Design Part 3: Excel Data Types – Strange COUNTIF behaviour

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 … Continue reading

Posted in Calculation, Lookups, UDF, XLL | 5 Comments

The SpeedTools FILTER.IFS Function Design Part 2: Excel Data Types – When is a Number a String?

Excel Data Types Excel has only  4 or 5 native data types: Numbers (which can be formatted as Dates, Times, Currency, Integers, Doubles etc, but are all held internally as floating point doubles) Strings (Text including zero length strings like … Continue reading

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

The SpeedTools FILTERIFS function: Design and Implementation Part 1

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