Excel’s Custom Lists are great, but you need to drag them to create them. If you have a list that you use frequently why not create a custom function to display it? This has the added advantage of creating a spill range that can then drive other dynamic array formulas.
When working with YTD percentages you must be careful with the calculation. Adding up values or amounts is easy. Working with YTD percentages require a bit more work.
When I started learning Python, I saw it had a Reverse function and thought, “I could do that in Excel.”
In the previous post I created a custom function that creates a horizontal list of months based on three inputs. In this post I share a few ways that you can use this list.
It is common in reporting files, budgeting and forecasting files to create a list of months going across the page. The custom function in this post can automate the process.
A recent update to Excel included ordinals as an option when you drag with the fill handle. A few months back I made a post about a Custom function for ordinals in dates. Dates are a bit easier because they max out at 31. I thought I would extend the function so that it handled all numbers.
The UNIQUE function in Excel has a problem when it comes to handling blank cells. Blank cells are treated as zero and if you have a blank cell and a zero in a range then UNIQUE will return two zeros. Also, if a cell has a function that returns a blank cell, then that is treated differently than a blank cell. Let’s create a custom function to fix UNIQUE’s blank cell blind spot.
Excel uses a number from 1 to 7 to refer to the days of the week. The WEEKDAY function returns the number based on a date. Excel doesn’t a have a function to return the day number based on a text day like Monday. Time for a custom function.
I have previously covered a formula that counts specific characters. Since it used multiple functions, I thought I would simplify it by creating a custom function.
Benford’s law is used in auditing to identify data sets that may have been manipulated or adjusted. In my previous post I created a report to analyse a data set based on Benford’s Law. In this post we will create a single formula to create the report and then convert that into a custom function.
Benford’s law is used in auditing to identify data sets that may have been manipulated or adjusted. In actual data sets when reviewing values the 1st digit of the values tends to follow a predetermined frequency. For example, roughly 30% of the values should start with a 1.
The SUBTOTAL function in Excel is quite flexible. The single function allows you to perform 11 different calculations. In this post we will create a custom function to summarise a data set.
In last week’s blog post I covered a complex formula to return unique random whole numbers. In this weeks’ post we will look at how we can convert that complex formula into a custom function.
A few years back I posted a series of three posts about developing a formula to create a month calendar in Excel. I thought I would revisit that and convert that long formula into a custom function using LAMBDA.
Excel has new TEXTBEFORE and TEXTAFTER functions. It doesn’t have a TEXTBETWEEN function. Let’s make one.
Calculating age with years months and days takes several functions to create. Whenever you need multiple functions to calculate an entry you may have a good use case for a custom function.
Excel’s EOMONTH function is great. Here is a tweak using a custom function to calculate the number of days in a month.