Averaging is easy business in Microsoft Excel. Thanks to the AVERAGEIF() and AVEREAGEIFS() functions, conditional averages are also easy until you want the condition to be dynamic. For instance, if you maintain a table of monthly sales figures and someone asks you for the ability to average over the last n years, you might scratch your head and feel a bit overwhelmed.
Fortunately, it’s simpler than you might think to accommodate that n request. In this tutorial, I’ll show you how to combine commonly used features and functions to return monthly averages for the last n years. The technique also lends itself well to a dashboard.
I’m using Microsoft 365 desktop on a Windows 10 64-bit system, but you can use earlier .xlsx versions. Excel for the web fully supports this technique. For your convenience, you can download the .xlsx demonstration file.
SEE: Feature comparison: Time tracking software and systems (TechRepublic Premium)
How to find average quickly in Excel
If you need the average for a one-off task, you don’t need the majority of this tutorial. Instead, there’s an easy way to return what you need without functions, formulas, PivotTables and so on. To return the average sales for any period, do the following:
- Select the sales figures for the years you want to average. For example, if you want the average sales for the last two years 2021 and 2020, select C3:C26.
- On the taskbar, you’ll find the average for the selected values. In this case, it’s $1,356,157, as shown in Figure A.
- Double-click the average on the taskbar.
- Select the cell where you want to enter this average and press Ctrl + V.
Figure A
As you can see in Figure B, you can copy the average from the taskbar into your sheet. This is a fairly new feature, so you might not have heard of it yet. If you don’t need to calculate this value again, use this quick method for returning the average of the selected values.
Figure B
When you need to return the average sales for n years on a regular basis, you’ll want something more permanent in place.
How to generate the year from the date in Excel
If you’re lucky, your data will contain the year as a lone value. Our example sheet (Figure A) has two columns: A monthly date and a sales value for each month. That means the first thing we have to do is populate a column with the appropriate year for each date. In a real-world situation, you might have data for dozens of years, but we’re stopping at four for the sake of simplicity.
To return a year for each date value in column B, enter the following function into D3 and copy it to the remaining records:
=YEAR([@Month])
Use this function if you’re not using a table:
=YEAR(B3)
Figure C shows the results. If this simple function returns an error, check the date value because most likely it isn’t a valid date. Our next step is to add a matrix based on a unique list of year values.
Figure C
Now we need a way to work in the n component, which will limit the sales values evaluated.
How to accommodate n
The next step is to accommodate the n component, which will determine how many years you want to evaluate. Figure D shows a few new labels and expressions. The expressions in column I are the expressions in use in column G.
Figure D
In a nutshell, G3 is an input value — it equals the number of last years you want to evaluate. For example, if you enter 2, the two last years are 2021 and 2020, if you enter 3, the last three years are 2021, 2020, and 2019, and so on.
The MAX() function returns the latest year in the Sales Table (B2:D50). You could also enter the latest year as a literal value. Either way works but using Max() negates the need to know the last year. It isn’t necessary to sort the data for everything to work. Depending on how much data there is and where it came from, it might be difficult to determine the latest year visually.
The expression, =G3-(G2-1), returns the oldest year in the Sales Table that’s evaluated. G3 is always the last year with the current data. This expression subtracts from the latest year in G2 to determine the earliest year included in the average.
The last expression that we’ll look at, =IFERROR(AVERAGEIF(Sales[Year],">="&$G$4,Sales[Sales]),""), does most of the heavy work. The AVERAGEIF() function uses the criteria component,”>=”&$G$4 to determine the years represented by n. For example, if you enter 2, this component evaluates as follows:
">="&$G$4
">="&2020
>=2020
Consequently, the AVERAGEIF() function only evaluates sales values where the year in column D is greater than or equal to 2020. In our table, this means years 2020 and 2021. The IFERROR() catches errors generated when the input value isn’t 1, 2, 3 or 4 — because the table only contains four years of records.
You could also use a data validation control to reject input values other than 1, 2, 3 or 4. Simply select G2, click the Data tab and then choose Data Validation in the Data Tools group. In the resulting dialog, choose Whole Number from the Allow dropdown, Between from the Data dropdown, and then enter 1 and 4 in the Minimum and Maximum fields, respectively, as shown in Figure E.
Figure E
As you can see in Figure F, the average monthly sales for the last two years is $1,356,156.88. Try entering 1, 3 and 4 to represent the last year, the last three years and the last 4 years, respectively. Then try entering 0 or 5. The data validation control rejects invalid input values.
Figure F
Now delete the value in G2 and see what happens. Were you expecting an error? The ISERROR() catches that error and returns an empty string (“”) instead of an error.
Everything is dynamic as long as you use a table object for the original data. If you don’t, the expressions used to accommodate n won’t update as you add and delete records.
