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.
“Logic will get you from A to B. Imagination will take you everywhere.”
Albert Einstein
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.
“We know what we are but know not what we may be.”
William Shakespeare
Double Click the Excel Icon
You can close Excel down (with multiple files open) by double clicking the Excel icon – top left of screen.
This works for the other Office apps too.
If you haven’t saved a file Excel will ask if you want to.
To close a single file down use the X on the top right of screen.
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.
A wonderful discussion about creativity and how we can all be creative.
Makes you think.
“Play is the highest form of research.”
Albert Einstein
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?
Clear all formats
To clear all the formats for a cell or a range select the cell/range and press in sequence Alt H E F don’t hold the keys down.
All the formats will be removed.
If you need to see the underlying number for a date this is an easy way to find it.
Solving a Date – Time Issue in Excel
Often when you import data into Excel the dates may include a time. This can make summarising the data more difficult as time is included as a fraction of a date. You can use an old function to fix the problem. This solution requires the subscription version of Excel.
IFERROR Function Now Handles SPILL Errors
Not sure when it happened, but a recent update to Excel has enabled the IFERROR function to handle spill errors.
Creating A Text String of Lowercase And Uppercase Letters
As a follow on from last week’s post you may want to create a text string of all of the lowercase and uppercase letters. Again we can combine some Excel functions to achieve this.
Listing Alphabetic Letters in Excel
If you need a listing of the letters of the alphabet you can combine a couple of functions to provide the list.
Excel Drop Down Improvement
In previous versions of Excel when you had a drop-down list that contained duplicates those duplicates would show up in the drop-down list. This has been fixed in the latest versions of Excel. The duplicates are now removed.
You have to finish things — that’s what you learn from, you learn by finishing things.
Neil Gaiman – author
Updating the Excel Header with a Cell Value
A common request over the years for Microsoft has been to have the ability to link the print header or footer to a cell. As at the time of writing this functionality doesn’t exist, but we can add a one-line macro to fix that.
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.
If you knew how much effort went into it you would not call it genius.
Michaelangelo
Solving a Conditional Summing Text Problem in Excel
I was checking out an old Excel book Excel Outside The Box by long time Excel MVP Bob Umlas and noticed he used the N function in his SUMPRODUCT functions. I then realised why. It converts text to a zero. That gets around an issue with adding up ranges that contains text, thanks again Bob.
“Everything not saved will be lost.”
Nintendo “Quit Screen” message
A New Month of Sundays
One of my more popular posts involved counting the number of Sundays between two dates. With dynamic arrays that becomes easier, and we can create a custom function.
It’s so hard to forget pain, but it’s even harder to remember sweetness. We have no scar to show for happiness. We learn so little from peace.
Chuck Palahniuk (Novelist)
May the Macros Be With You
May is Macros month.
You can learn how to save time and effort by replacing repetitive or time consuming tasks with a macro. Macros make delegation easier.
Four live Excel webinars to get you started with macros. The first one is free. Over 5 hours of training.
Buy the Essential Macros Bundle for AU$60 and be registered for all the live May sessions (see below for dates) plus get access to the online courses including bonus macro content.
Essential Macros Bundle 2024Can’t push objects off the sheet Error in Excel
I recently had an error pop up when working with a client’s file. The client’s file had a macro that would hide most of the columns on the sheet, but it had started to generate an error message.
Deeds will not be less valiant because they are unpraised.
Source: Aragon in The Return of the King
J.R.R. Tolkein
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.
Padding Entries in Excel
Another post inspired by the book 101 Ready-to-Use Excel Formulas by Michael Alexander and Dick Kusleika. This one is Formula #22 and covers padding entries with zeroes.
Introduction to Dynamic Arrays Webinar Recording
Learn about the new way to create formula and functions in Excel. This webinar recording from April 2024 will get you started.
Significant Digits in Excel
This post is inspired by the book 101 Ready-to-Use Excel Formulas by Michael Alexander and Dick Kusleika. Formula #10 allows rounding to a certain number of significant digits. This post shows how to convert that formula into a custom function.