# 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.

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.

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.

# 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.

# 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.