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…)
Excel has plenty of issues when it comes to correctly handling blank cells. Here is a solution to one of those issues.
(more…)
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…)
I was recently reminded of a basic programming challenge called FizzBuzz. Here is my Excel solution.
(more…)
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…)
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…)
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…)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.
Hyperlinks in Excel are a great way to navigate around a file, but they can be easily broken if the sheet name changes. Try these solutions using a formula to create hyperlinks that don’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 a…
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.
(more…)
I recently saw a post of LinkedIn (from Patryk Samborski) that used the percentage symbol with the SEQUENCE function to produce a list of decimals and I thought I would have a play with that idea.
(more…)
I recently received a request to help with a salary packaging calculation. I thought I would share the solution and explain the technique to solve it. This is a case where we have a value we need to equal but don’t know the components that make it up. We are in effect working backwards to find the missing value.
(more…)
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…)You can use conditional formatting to insert symbols in cells. You can also use formulas with emojis. using range names makes it even easier. To insert an emoji icon in a cell you can use press the Windows key and the full stop. This opens the Emojis dialog box. In this example we are going to insert three separate symbols in formulas. I have named each cell that has an emoji. A1 = Tick, A2 = Cross and A3 =…
The typical range reference looks something like A1:A10. You always refer to the top left cell followed by the colon followed by the bottom right cell of the range. Did you know Excel can handle you entering the last cell followed by the first and it corrects it for you?
(more…)
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…)
Creating a Dynamic list of dates in Excel is pretty easy now with the use of the SEQUENCE function. Creating a dynamic list of weekday dates is a little bit more complex.
(more…)
I have created a custom function to check if a range has sequential numbers. The range doesn’t have to be sorted.
(more…)
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…)
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.
(more…)
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.
(more…)
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…)
A while ago I posted about creating an ordinal (1st, 2nd etc) for a date. With the inclusion of the LAMBDA function I thought I would create a custom function to simplify the process.
(more…)
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.
(more…)
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.
(more…)
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.
(more…)I am not a fan of the merged cell format. It causes more problems that it solves. One issue you will face is trying to select a single column range within a range that has a merged cell. Here is how you handle it. This post is a video post as it easier to show the problem and the solution in a video. https://vimeo.com/739153569 This post is a video post as it easier to show the problem and the solution…