This article shows you how to calculate monthly averages from a data table containing dates and values using AVERAGEIFS and EOMONTH functions.
If you only want to calculate the number of days in a month, please refer to the article: How to find number of days in month
Syntax
=AVERAGEIFS(
numeric data range,
date range,
">=" & first day of month,
date range,
"<=" & EOMONTH(
first day of month,
0
)
)
Steps
- Start with =AVERAGEIFS(
- Select or type the range reference that includes the numeric $C$3:$C$12,
- Continue with the first criteria range – criteria pair with date range and 1st day of month $B$3:$B$12,">="&$E3,
- Enter the second criteria range – criteria pair with date range and EOMONTH function $B$3:$B$12,"<="&EOMONTH($E3,0)
- Type ) to close AVERAGEIFS function and press Enter to complete formula
How
The AVERAGEIFS function calculates the average of values that meets a single or multiple criteria. The ability to use criteria with logical operators like greater than or equal (>=) and less than or equal (<=) provides the way of evaluating values between limits.
To filter dates in a month, we need dates for the first and last days of that specific month. Although the first day of the month is easy to get, showing a particular date like 11/1/2018 is not visually appealing to represent a month. Thanks to formatting options in Excel, we can display a full date as its month name only. Adding custom format "mmmm" to a date will display the name of the monthwithout changing its original value. To apply a custom format:
- Select the cell to be formatted and press Ctrl+1 to open the Format Cells dialog.
- Under Category, select Custom.
- Type in the format code into the Type
- Finally, click OK to save your changes.
For detailed information on Number Formatting please visit: Number Formatting in Excel – All You Need to Know.
After adding the first days of months, it is time to enter criteria range-criteria pairs. The first pair is easy: date range and the date of the first day of the month.
$B$3:$B$12,">="&$E3,
Next criteria range-criteria pair should handle the last of the month. We use the EOMONTH function. The EOMONTH requires date and month arguments. The date is the same date we use for the first criteria and month argument is set to 0 to point exact date in the first argument.
$B$3:$B$12,"<="&EOMONTH($E3,0)
Finally, because both criteria range-criteria pairs are set, it is time to use them in the AVERAGEIFS function with range that includes values to calculate monthly averages.
=AVERAGEIFS($C$3:$C$12,$B$3:$B$12,">="&$E3,$B$3:$B$12,"<="&EOMONTH($E3,0))
