SUMIFS can use wildcard characters, but the wildcards only work on text-based codes.
Tag Archives: formula
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.
Custom Function for an Ordinal Date
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.
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.
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.
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.
Selecting a Column Range within a Merged Cell in Excel [video]
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.
MIN and MAX and Dates in Excel
Automating the latest date
The MIN and MAX functions can provide easy ways to capture current dates.
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.
Adding up Text Numbers In Excel Another Technique
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.
The Magical N Function in Excel
Who you calling short?
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.
Excel Variance Formula
IF function to the rescue
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.
Financial Year Month in a Pivot Table
Create new column
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.
Convert text time to real time in Excel
Three different ways
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.
Find the Closest Value in Excel
Dynamic array solution
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.
Monitor Cells with Excel’s Watch Window
Don't wait, watch
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.
Related Posts
Twenty Years Ago – Top 10 Excel Functions
How things change
My second article in Accountants Weekly was published 20 years ago today and it was Top 10 Functions for Accountants.
Adding Time in Excel
There's a function for that
If you need to add time to an existing time then you need to learn about the TIME function.
Confirming Names are Unique in Excel
COUNTIFS to the rescue
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.
Make Sure All Input Cells Have an Entry in Excel
COUNTIF to the rescue
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.
Australian Financial Year Quarter Formula
Another CHOOSE solution
The Australian Financial Year has its challenges. Working out the Quarter number based on a date has a few solutions. Here’s another one.
Financial Model Allocation Technique
Helper cells to the rescue
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.
Handling Formula-Based Blanks
The N function to the rescue
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.
Single formula for a Column
It can done
In Excel your goal should be to have a single formula in a table column that can be copied down the whole column.
Treating text as zero in Excel
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.
Text for a week in Excel
Time to TEXT
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.
Using SEQUENCE with Dates in Excel Part 1
Date listing
The SEQUENCE function returns sequential numbers. Let’s see how we can use it with a list of dates.
Time to Play with Dynamic Arrays
Let play time begin
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.
Switching Rows and Columns is Now Easier
TRANSPOSE rules
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.