Excel Formula for Duplicates Revisited
I was reviewing a blog post from last year that listed duplicates in a range. I thought I could shorten the formula using the LET function.
(more…)Handling Different Column Structures in Excel
One thing you learn in Excel is that there are so many ways to display the same data. When working with Actual / Budget / Variance one structure can be a challenge to work with. See how the FILTER function can simplify it.
(more…)The only time to use SUMIF
I no longer teach the SUMIF function. I teach the SUMIFS function as it provides more solutions because it handles multiple criteria. The two functions differ in their argument sequence which can be confusing when switching between them. Rather than learning both, it is easier to learn the SUMIFS function. There is however one time when the SUMIF function is shorter and easier to use than SUMIFS.
(more…)Flexible SUMIFS Function
The SUMIFS function is a conditional sum function that allows you to handle multiple conditions. Here is a technique to handle the situation where a condition maybe blank.
(more…)Two Column Lookup in Excel – Part 1
It is common to perform a lookup based on one column. Performing a lookup based on two columns is more unusual. This is a formula technique you can use to perform a two-column look up.
(more…)Excel SEARCH and Wildcards
Excel has recently added three regex functions to handle text patterns, but did you know the old SEARCH function can use wildcards?
(more…)TRANSPOSE and TOROW Functions in Excel
When you have a vertical list that you need to make horizontal you can use the TRANSPOSE function to switch it. There is an alternative function that gives you extra options.
(more…)Excel FizzBuzz Challenge
I was recently reminded of a basic programming challenge called FizzBuzz. Here is my Excel solution.
(more…)TRIMRANGE Syntax and INDEX and INDIRECT
The INDEX function can return a reference to a cell. However, the new TRIMRANGE syntax won’t accept it a reference. You need to use the TRIMRANGE function to use INDEX or INDIRECT. In my last post I showed how to always refer to cell A1 in a reference. The techniques demonstrated in that post won’t work with the new TRIMRANGE syntax. – see this previous blog post about the new syntax. If you try to use the new syntax, you…
Making Sure a Reference is Always A1
When you create a formula or a range that refers to cell A1 and you want it to ALWAYS refer to A1 no matter what – you have at least a couple of options.
(more…)Excel Variance Techniques
When comparing actuals to budget or this year to last year you often calculate the variance and variance %. The variance calculation can be standardised with one simple trick and another function can make sure the variance % has the right sign.
(more…)New TRANSLATE Function
A recent update to Excel includes a new TRANSLATE function. This function enables you to translate words from one language to another.
(more…)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.
(more…)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.
(more…)Olympic Average in Excel
Averages are affected by outliers. If Bill Gates walks into a room the average net worth per person jumps substantially. In the Olympics some sports deduct the top and bottom scores before calculating the average score. Here’s a formula to do that in Excel. You need the subscription version of Excel for this solution.
(more…)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.
(more…)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.
(more…)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.
(more…)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.
(more…)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.
(more…)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.
(more…)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.
(more…)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.
(more…)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.
(more…)SUBTOTAL and Dynamic Arrays in Excel – Part 4
The SUBTOTAL function in Excel is quite flexible. The single function allows you to perform 11 different calculations. In this post we will amend the custom function we have created to add an extra column plus headings.
(more…)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.
(more…)SUBTOTAL and Dynamic Arrays in Excel – Part 2
The SUBTOTAL function in Excel is quite flexible and in this second post we build an automated summary report using SUBTOTAL.
(more…)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.
(more…)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.
(more…)