Garreth Hanley:
This is Excel Tips, a podcast for accounting and finance professionals brought to you by CPA Australia.
Neale Blackwood CPA:
Custom functions, episode overview:
Welcome to Excel Tips. My name is Neale Blackwood, and in this episode we're going to be talking about two custom functions that solve two separate issues with Excel formulas. The first function solves an Excel formula problem, and the second solves a problem with the Australian financial year. So, Excel now lets you create your own functions, called Custom Functions, using the Lambda function and a range name. You can simplify long and/or complex calculations into a single function with multiple arguments. And then these can be used across files and even shared between users. These custom functions do require the subscription version of Excel.
Now, custom functions have been covered in a past episode, but those were simpler. The two custom functions I'm talking about today are more complex to create, but they're also more flexible and useful, and you don't even need to create them because there's a companion file that has them in there. So check out the show notes for links to the companion article, the video and the companion file. The article and the video go into detail about how you create these two functions.
Summing spill ranges:
So the first custom function that I want to talk about solves a problem when you need to sum a spill range in Excel. So, a spill range is associated with dynamic arrays, and I've discussed those a lot in previous episodes. Spill range basically has a formula in the top left-hand corner and it can spill down, across, or down and across. And those type of spill ranges, the two-dimensional ones, they have been difficult to sum up the individual rows and the individual columns.
And so Excel released two functions, By Row and By Col, to allow you to sum up the rows and columns in a spill range. The problem is the formula is slightly different to sum up rows compared to columns. Also, you have to use three functions in combination to add up a row or add up a column. So that's an ideal situation for a custom function. And so the custom function that I've created allows you to select the spill range and then supply either R for rows or C for columns. So it's a single function that will add up either rows or columns in a spill range.
Using prefixes in functions:
So with custom functions, I do recommend that you use a prefix on the name. So, the custom function for summing, I've used ‘fn’ in lowercase and ‘SUMDA’ in uppercase. Obviously, Excel's functions are all in uppercase. By having a prefix in lowercase, that differentiates your custom functions from Excel's built-in functions, which is a good thing because then you know it is a custom function. It's not one of Excel's new functions. Also, when you type in ‘fn’, then you'll get a list of all of your custom functions in the file.
So there's two good reasons to use a prefix. Now, as I said, I use the ‘fn’ prefix, short for function. I've also seen FX used, and I've also seen the Lambda symbol used. Though, that one is a little bit more complex to insert into the range name, which is why I don't do it. But whichever one you use, ‘fn’ or FX, it's a good idea to have that as the prefix.
Setting up a switch function:
Now, the SUM function, the ‘fn SUMDA’. DA stands for dynamic arrays. You could call it, I guess, SUM Spill. The name you use is up to you. So use a descriptive name. So this ‘fn SUMDA’ function uses the SWITCH function. Now, the SWITCH function is really cool in that it allows you to create a lookup table within a function. So it's a standalone lookup table. Now, it does take a little bit of effort to create it, obviously, but it does give you the ability to use a lookup table within a function.
In this case, it was only looking up two things. So when you use this function, you provide the spill range with the hash symbol on the end and then a comma, and then you return within quotation marks either the R to add up rows or the C to add up columns. And again, when you're using text, you do need to enclose them in the quotation marks.
So that second argument was used by the SWITCH to determine which calculation to perform, because rows use the By Row function and columns use the By Col function. And so we've combined them into the one function to solve the problem of summing up the spill ranges.
Financial year custom functions:
The second function that solves the problem with our financial year, I've called ‘fn FY’, short for financial year. When I was researching different financial years, I found out that the English personal tax year starts on the 6th of April and finishes on the 5th of April the next year. That's unusual dates, and it came about due to a combination of religious holidays that set the start date of their financial year and then a change to the Gregorian calendar from the Julian calendar. So all of that ended up starting on the 6th of April. You'll be pleased to know that the English government's financial year and the corporate tax year start on the 1st of April.
Setting up a financial year function:
Now, financial years. So, our financial year starts in July. This custom function will handle a financial year that starts in July or April, or the other month that financial years start in is October. So with our financial year starting in July, getting the financial year month number and quarter number based on a date can be challenging, and that's what this custom function is going to fix.
So basically, you provide this function with the date, then a comma, and then you tell it the start month number of your financial year. In our case, that would be seven, and then another comma, and then you can return either the year, so you use a Y, you can return the month number, so you use an M, and a quarter, you can use a Q. And so that, again, single function will return either the financial year, the month, or the quarter of that financial year, which you can specify with the start month of the financial year. That function also used the SWITCH function, which created the lookup table for those entries. So again, when you're using text, you need to enclose the text within quotation marks. So the Y, the M, and the Q all need to be enclosed in quotation marks.
Jacqueline Blondell:
If you're enjoying this podcast, you should check out our in-depth business and finance show, INTHEBLACK. Search for INTHEBLACK on your favourite podcast app today.
Neale Blackwood CPA:
Using custom functions across multiple files:
One of the beauties of custom functions is that if you copy the formula from one file to another, you automatically copy the custom function across to the other file. So once you've created these custom functions, you can use them across multiple files, and you can even share them between users. So if another user opens up the file, they can copy it across to their file and use it in their file. So it's really, really flexible. It might take a little bit of effort to create the custom function and test it initially, but once you've done that, then the custom function is able to be used by you and other people.
Custom functions overview:
So, custom functions are exciting. You can create your own functions to do your own calculations. As I said, they can simplify the calculation. Creating the Lambda function, that is not simple. So that does take a little bit of practice to get your head around, and again, the video goes into detail about that. But once you've got that, that's in the background, using the custom function is really, really simple. Which is what the aim of the custom function is, to simplify complex formulas. I hope you found that useful. Thanks for listening.
Garreth Hanley:
Thanks for listening to Excel Tips, a CPA Australia podcast. If you've enjoyed this episode, help others discover the show by leaving us a review or sharing this episode with colleagues, clients, or anyone else looking for the best Excel tips and tricks. To find out more about our other podcasts and CPA Australia, check out the show notes for this episode. And we hope you can join us next time for another episode of Excel Tips.
