SUMIFS can use wildcard characters, but the wildcards only work on text-based codes.
Category Archives: Functions
Calculating YTD percentages in Excel
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.
Percentage Movement in Excel
It is common in Excel to calculate the percentage movement or difference between two values. This may be between this year and last year or between actual and budget. There are two common issues you will face when doing this calculation. The first is handling zeros and the second is handling negatives.
FILTER Function Technique
An application I use recently updated it’s filtering options to allow you to filter by any filters or all filters. This was a useful addition to the software and I thought that I could apply the same idea to Excel’s FILTER function.
Benford’s Law in Excel – Part Two
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.
Comparing Averages in Excel
It is common in Excel to use averages to summarise large data sets. It is also common to compare the averages across different segments. Here’s a technique you might find useful when comparing a segment against all other segments.
SUBTOTAL and Dynamic Arrays in Excel – Part 3
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.
SUBTOTAL and Dynamic Arrays in Excel – Part 1
The SUBTOTAL function in Excel is quite flexible. The single function allows you to perform 11 different calculations. It can also ignore hidden rows, something that not many Excel functions can do.
Highlighting Weekends in Excel
When you have a list of dates in Excel it can be useful to know which of those dates are weekends. You can automate a format for weekends using a conditional format.
Unique Random Whole Numbers in Excel
Excel has three functions that can provide random numbers. But the random numbers created may not be unique random whole numbers. Here is one way to get a list of unique random whole numbers.
Average Top and Bottom Values in Excel
When analysing data you may want to check for outliers. You can use MIN and MAX to get minimum and maximum values but you may want to average a certain number of top or bottom numbers. Here’s how you can do it.
TEXTBETWEEN function in Excel
Excel has new TEXTBEFORE and TEXTAFTER functions. It doesn’t have a TEXTBETWEEN function. Let’s make one.
Percentage of the Year in Excel
As we get used to the new year we may want to perform some calculations based on the old year. A recent inquiry requested a formula that could calculate the percentage of a year that an employee had been employed. He suggested using an IF function. See the solution below, but it doesn’t involve the IF function.
Rounding to the Nearest 9 Cents in Excel
A client recently requested a formula to round to the nearest 9 cents. This avoids getting to a price point. This is a common requirement in retail businesses. The solution was simpler than I thought it would be.
Custom Function for Age in Excel
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.
Last Weekday of the Month in Excel
Excel has a function to find the last day of the month. To find the last weekday of the month you can combine a couple of functions. Here is a custom function that also works.
Custom Function for Days in the Month
Excel’s EOMONTH function is great. Here is a tweak using a custom function to calculate the number of days in a month.
A Cell Reference Custom Function
The formula to return a cell reference is quite long. This makes it an ideal candidate for a custom function.
Date Alignment Trick in Excel
Text alignment in Excel is versatile. If the column isn’t wide enough to display the text, it will display over the next cell. Date and number alignments are not so forgiving. If the column isn’t wide enough the cell with display the ### symbols or the scientific format. Here is a function technique to get around the limitation.
Not So Random Entries in Excel
Sometimes when you are testing or training in Excel you need to create random entries. That is easy to do with the RANDBETWEEN or RANDARRAY functions. What if you wanted to emphasise some entries more than others in the random list created? There is a way.
Avoid the #DIV/0! Error in Excel
DIY Function
If you have the subscription version of Excel you can create your own functions. One that you may want to create avoids the #DIV/0! error.
MIN and MAX and Dates in Excel
Automating the latest date
The MIN and MAX functions can provide easy ways to capture current dates.
One Minute to Excel #26 – Fix AutoSum’s Weakness
Get the right range every time
AutoSum’s cryptonite is a blank cell – it stops AutoSum in its tracks every time.
Here’s how you can avoid AutoSum’s blind spot.
UNIQUE Function and Blank Cells in Excel
Zero in on a problem
The UNIQUE function has a bit of an issue with blank cells, formulas that return blank cells and zeroes.
Identifying if a List has Unique Entries in Excel
A MODE solution
If you need a logical test to determine if a list is unique you can use the MODE function with the ISNA function.
Adding up Text Numbers In Excel Another Technique
If you have a list of numbers that are a text numbers or a combination of text numbers with real numbers there is a technique I covered in this blog post to add them up. But if the range also contains text then the technique won’t work. There is the work around. The solutions below work in the subscription version of Excel. Check the comments section below for a solution for all versions.
The Magical N Function in Excel
Who you calling short?
One reason I like the N function is because it is Excel’s shortest function name. But it has quite a few useful features as well.
Last used column number in a row in Excel
AGGREGATE to the rescue
A while back I posted a formula to find the row number of the last used cell in a column. I revisit the solution to provide the last used column number in a row.
Removing Outliers in Excel
Dynamic array solution
I wrote a blog post a while back about outliers and Excel and I thought I would revisit it thanks to dynamic arrays.
The Excel CONVERT function
Most conversions are easily done
If you need to convert between different measurement systems Excel has just the function for you, called CONVERT.