SUMIFS can use wildcard characters, but the wildcards only work on text-based codes.
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.
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.
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 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.
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.
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.
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.
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.
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.
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.
Excel has new TEXTBEFORE and TEXTAFTER functions. It doesn’t have a TEXTBETWEEN function. Let’s make one.
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.
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.
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 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.
Excel’s EOMONTH function is great. Here is a tweak using a custom function to calculate the number of days in a month.
The formula to return a cell reference is quite long. This makes it an ideal candidate for a custom function.
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.
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.
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.
The MIN and MAX functions can provide easy ways to capture current dates.
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.
The UNIQUE function has a bit of an issue with blank cells, formulas that return blank cells and zeroes.
If you need a logical test to determine if a list is unique you can use the MODE function with the ISNA function.
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.
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.
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.
I wrote a blog post a while back about outliers and Excel and I thought I would revisit it thanks to dynamic arrays.
If you need to convert between different measurement systems Excel has just the function for you, called CONVERT.