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?
Testing Speed
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...
The UDF
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.
Conclusion
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.
For more Excel tips from Yoav, join him on Facebook or Twitter
4 thoughts on “User Defined Functions vs Formulas in Excel – Guest Post”
What about writing asynchronous UDFs--wouldn't that speed up the calculations?
Understandably perhaps, but I think you have over-simplified the argument. UDFs can often be far quicker than formulae, given the right circumstances, and good UDF design, and you can avoid having those interminably calculating workbooks.
And of course, formulae are an auditing nightmare, especially on large workbooks, especially when those pesky defined names are used.
Claire - I don't think so, in fact, I think that it will take longer since you'll have all that swapping to do.
Bob - You are correct, as the formula becomes more complicated then a UDF will be easier to understand and maintain and overtake the formula in performance, but for simpler formulas, formulas will almost always win the performance test.
I agree Yoav, that is why I used the word 'understandably' in my response; I was just trying to point out that in real world situations, UDFs may well be the best, the only practical, option.