In the previous post we looked at extracting initials when we only had a first name and a last name in this post, we will look at handling more than two names.
Category Archives: Functions
Extracting Initials in Excel – Part 1
Extracting initials in Excel can be challenging. That’s because the names can be separated by different characters and there can also be more than two names. Some new functions in Excel can simplify the extraction of initials.
A Better SEARCH Function for Excel
The SEARCH function has a couple of issues that make it difficult to use. This makes it a prime candidate for a custom function to fix its limitations.
More Adjusting in Excel
Following on from last week’s post on a single adjustment formula this post will share a more robust solution for including or excluding adjustments.
Adjusting in Excel
One of the advantages with Excel is that you can usually handle exceptions. In this post I examine a way to handle exceptions or adjustments without using the IF function.
SUMIFS Wildcard Limitation
SUMIFS can use wildcard characters, but the wildcards only work on text-based codes.
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.