The following article is courtesy of Yoav Ezer, who volunteered to do a guest post for me while I am at the summit. Thanks Yoav!
User Defined Functions versus Formulas in Excel
Which are better, Excel functions or UDFs? Use Microsoft Excel for long enough you are inevitably going to get to a situation where you are going to have to choose. In many cases they might seem equivalent. What are the pros and cons of each approach?
The main difference of course is that the developer of the spreadsheet designs the User Defined Function, it is user-defined after all! This means the function is exactly what you need right now, whereas formulas depend on using the (albeit powerful) built-in features of Excel.
In terms of readability, you will usually find well-written functions have the slight edge. Rather than a complex formula with potentially many levels of nested commands, you can simply use MyFormulaName() and so on.
What other comparisons can be made?
To test to see if there was a significant speed difference we set up a spreadsheet as below. On the left we have some columns where we test to see if the number in column C is between the number in A and B. We use a function to check. Over on the right we do the same thing, but this time using formulas.
To fill out the rows, we increment the values in both sets of columns just to give us some values to work with.
In D our cell contains a very easy to read =GetBetween(C2,A2,B2)
In column I we have our formula =IF(H2=MEDIAN(F2:H2),"Yes","No") which you may or may not find easy to read!
Obviously the function is hiding a bit more complexity...
Our UDF is called, as you would expect, GetBetween and takes a value and two cell references. We check to see if the value is between the two cell values. It's a simple IF statement to return our answers.
Which is Faster?
I copied these cells around 50,000 times and changed the values.
Disappointingly, at first I found no discernable difference. It seems in this case the formula and the UDF are pretty equivalent. Until, that is, you start to tax your computer. When I doubled that number of rows I started to see that UDFs can be almost twenty times slower than formulas.
It seems in normal day-to-day usage there is no clear winner, so use whichever approach works for you. In terms of developer flexibility the UDF will ultimately win but until you get to that threshold work with whichever approach suits your objectives and skill levels.
Once you start to build huge spreadsheets though pure performance is definitely going to become an issue. That is where you need to start considering where you can make easy gains in speed, testing formulas over your precious functions might be the place to look.
What do you think? UDF or formula?
About the author
Yoav Ezer co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to Excel conversion software.