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.
Tag Archives: formula
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.
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.
Excel’s Magic Percentage Symbol
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.
Working Backwards in Excel
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.
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.
Using Emojis in Excel Formulas
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 = Dash.
You can use these names in formulas throughout the file.
The formula in cell F2 (Sales) is.
=IF(D2>E2,Tick,IF(D2<E2,Cross,Dash))
The formula in cell F3 (Costs) is.
=IF(D3<E3,Tick,IF(D3>E3,Cross,Dash))
The advantages with using formulas instead of conditional formatting is that you can format the cells. Plus using formulas in cells is easier than using formulas in conditional formats.
Naming your emojis makes then easier to use. You can use these emoji icons names in your formulas throughout the file.
Excel and Range References
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?
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.
Dynamic List of Weekday Dates in Excel
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.
Does an Excel range contain sequential numbers?
I have created a custom function to check if a range has sequential numbers. The range doesn’t have to be sorted.
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.
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.