SUMIFS can use wildcard characters, but the wildcards only work on text-based codes.
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.
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.
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.
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.
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.
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.
The MIN and MAX functions can provide easy ways to capture current dates.
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.
When calculating variances between actuals and budget, you typically have a positive value representing a favourable (good) variance and a negative value for an unfavourable (bad) variance. When looking at revenue and expenses together this poses a problem for the variance calculation. The calculation needs to be different for revenue and costs. Here’s a way to use a single formula for both.
I wrote an article years ago explaining how to use a related table to handle financial years in Excel Pivot Tables. You can read the article here. If you only want the months in financial year order you can just add an extra column to your table.
I recently downloaded an example file for an Excel challenge. The challenge had a lot of things to do but they were all based on a Timestamp column that had text instead of times.
On LinkedIn recently someone posted an Excel formula solution lamenting that it was long and complex. That of course was a challenge to me to simplify it.
When you are developing a file for a report, budget or forecast you may need to keep track of certain cells. They could be validations or profits or some other important value. The Watch Window can help you monitor multiple cells in one place.
In the Formulas tab in the Formula Auditing section is the Watch Window icon.
Clicking the icon opens the Watch Window. It will be blank.
You can click the Add Watch button to add a cell to monitor. You can chose cells from any sheet.
Click Add to watch the cell. You can widen the Watch Window and change column widths as well.
You can click the column headings to sort by the column.
You can also select a range to watch. but it will list the range as a series of cells – see images below.
When you save and close the file the watch entries are saved. When you open the file the Watch Window won’t be open, you will have to re-open it.
The shortcut Alt M W (pressed in sequence, not held down) will open the Watch Window. The same shortcut also closes the Watch Window.
My second article in Accountants Weekly was published 20 years ago today and it was Top 10 Functions for Accountants.
If you need to add time to an existing time then you need to learn about the TIME function.
If you have a list of first names and last names and you want to make sure the list has no duplicates you can use a formula to confirm the names are unique.
When creating an input range you may need to validate input cells. That may mean ensuring all input cells have an entry. Here’s how.
The Australian Financial Year has its challenges. Working out the Quarter number based on a date has a few solutions. Here’s another one.
In a financial model you often have different types of allocations that start at different times. Creating a short formula to handle this flexibility can be a challenge. Here is one solution.
It is common to display a blank cell using the IF function and “”. A problem can arise when you want to use that IF formula in a calculation. Here is an easy way to cope.
In Excel your goal should be to have a single formula in a table column that can be copied down the whole column.
Let’s say you are getting inputs you can’t control and in some cases you get text and others you get numbers. You want the numbers, but you need to treat text as zero. Here’s the easy way to do that.
When working with weeks in Excel you may need to show the start and end date of the week in the same cell. Here’s how you can do that.
The SEQUENCE function returns sequential numbers. Let’s see how we can use it with a list of dates.
Many things that were hard or complex are now much simpler. Creating dynamic drop down lists based on previous selections used to be tricky in Excel. Dynamic arrays make it straightforward.
With the introduction of Dynamic Arrays in all versions of Microsoft 365 (formerly Office 365) it is now a lot easier to use the TRANSPOSE function.