7 Steps To Perfect Formulas PLUS Bonus Cheat Sheet: 15 Of The Best Starter Functions
Enter your details for immediate access
In the previous post, you discovered how easy it is to calculate a total using the AUTOSUM button. In this post I expand on that and discuss 4 other basic formula functions that are also useful and easy to use - AVERAGE, MAX, MIN and COUNT.
Prefer to watch, instead of read?
All 4 functions are actually found on the AUTOSUM button, but are only displayed when you click the little drop-down arrow.
Let’s take a look at a simple table showing the number of kilometres driven each month.
I’m going to show you how to calculate the AVERAGE monthly distance, the LONGEST distance, the SHORTEST distance and HOW MANY monthly figures are in the list.
With each function, just like with AUTOSUM, you can start by selecting the blank answer cell
… OR the 6 cells containing the figures
… OR the 6 cells containing the figures AND the blank answer cell.
To calculate the AVERAGE monthly distance:
To find the LONGEST distance:
MAX can be used whenever you need to find the figure that is the LONGEST, TALLEST, LARGEST, MOST etc.
To find the SHORTEST distance:
MIN can be used whenever you need to find the figure that is the SHORTEST, LOWEST, SMALLEST, LEAST etc.
To find HOW MANY figures in the list:
Use COUNT whenever you need an answer to a HOW MANY type question.
Because COUNT NUMBERS only counts how many cells contain numbers, (hence the name) if a number is deleted or overwritten with text and/or symbols, these cells will be ignored and the count will decrease.
Whenever two or more cells are selected, a number of stats are automatically displayed on the status bar.
The stats available are
As you can see, they tie in closely with the functions covered above. The difference between COUNT and COUNT NUMBERS is that the first will count how many cells are populated while the second only counts the cells containing numbers. Both are useful.
To show more or less of these stats:
Don’t forget, once you have made your changes, you must first select two or more cells to see the stats appear on the status bar.
In the next and final post in this mini-series, I discuss the importance of the ‘Order of Operations’. It resurrects some old math skills from the archives of your brain but you need to know and master this before you go any further with Excel.
Even seasoned Excel pros sometimes get this wrong, and there have been stories of companies losing millions of dollars due to simple mathematical errors with formulas in Excel. Make sure you don’t make the same mistakes.
See you there.
Did this clear something up for you? If so, please share on your socials and/or post a brief comment below.
Likewise, if something didn't make sense or you have a question, pop it into the comments below. I'll be waiting ...
Cheers. Here's to your learning and success. Enjoy the rest of your day.
Please log in again. The login page will open in a new window. After logging in you can close it and return to this page.