A recent project required editing many formulas to insert an IF function to display the NA error in certain circumstances. Here’s how I did it.
Category Archives: Functions
Excel YTD Reporting Solution
Having Actuals, Budget and Variances for each month going across the page is a very common reporting construct. Here is an easy way to sum the correct YTD values as the year progresses.
Creating Sentences in Excel
If you want to automate journal descriptions or create sentences the TEXTJOIN function is your friend. It can combine words and insert the spaces for you.
Selecting a Formatted Table in a Formula
You can select a formatted table when you have a cell or range selected in the table by pressing Ctr + A. But that shortcut won’t work when creating a formula that refers to a formatted table.
To select the table in a formula you must click a cell in the table and press Ctrl + Shift + Spacebar.
Excel Hyperlink Formula Solution
Hyperlinks in Excel are a great way to navigate around a file, but they can be easily broken. Try this solution using a formula to create a hyperlink that doesn’t break so easily.
In the image below there is a formula in cell C3 that creates a hyperlink to cell A1.
Here is the formula.
=HYPERLINK("#"&CELL("address",A1),"<link text>")Simply change the cell reference from A1 to whatever cell you want to link to. This works for cell in the current sheet.
Hyperlinks to other sheets
In the image below is an example of a link to another sheet.
The formula is.
=HYPERLINK("#"&CELL("address",Report!A1),"<link text>")Again, change the reference to create a hyperlink that doesn’t break if the sheet name changes.
Pro Tip
To return after following a hyperlink press in sequence, function key F5 and then press Enter. Don’t hold them down just press F5 then press Enter.
Creating Full Names In Excel
When joining parts of names together, you may want vary the characters you use to separate the name. Depending on the effect you may be able to use array syntax to control the characters and their location.
Excel Auto-Adjusting Drop-Down List
If you need to have a drop-down list automatically adjust and remove items as they are selected, here’s one technique.
Hyperlink Drop Down List
I recently saw a post on LinkedIn about making a drop-down list for a hyperlink interface. It was using a range name and the ADDRESS function. I thought I could streamline it with just the CELL function.
Counting the Number of Cells in an Excel Range
Excel has functions to count the number of rows and columns in a range. It doesn’t have a function to count the number of cells in a range. We can still perform the calculation with the COUNTA function.
Solving a Date – Time Issue in Excel
Often when you import data into Excel the dates may include a time. This can make summarising the data more difficult as time is included as a fraction of a date. You can use an old function to fix the problem. This solution requires the subscription version of Excel.
IFERROR Function Now Handles SPILL Errors
Not sure when it happened, but a recent update to Excel has enabled the IFERROR function to handle spill errors.
SUMIFS Magic Solution
A client recently had a problem. He was chasing a formula that identified if a text string contained one of three words. He wanted to base an allocation on finding those three words. SUMIFS offers a solution.
Solving a Conditional Summing Text Problem in Excel
I was checking out an old Excel book Excel Outside The Box by long time Excel MVP Bob Umlas and noticed he used the N function in his SUMPRODUCT functions. I then realised why. It converts text to a zero. That gets around an issue with adding up ranges that contains text, thanks again Bob.
Extracting Initials in Excel – Part 2
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.
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.