Imagine this scenario: you have a dataset in Google Sheets and you want to calculate the average for each month. Sounds simple, right? Well, it can be a bit tricky if you don’t know the right formulas. But don’t worry, I’ve got you covered!
Average by Month in Google Sheets (Non-Array Formulas)
Let’s start with the non-array formulas. There are two types of formulas that you can use, depending on your needs.
Averageif Formula
The first formula we’ll explore is the AVERAGEIF formula. To use this formula, follow these steps:
-
In column B, enter the dates.
-
In column C, enter the corresponding numbers.
-
To calculate the average for a specific month, use the following formula:
=ArrayFormula(IFERROR(averageif(month($B$2:$B),11,$C$2:$C)))Replace
11with the month number you want to calculate the average for.
The AVERAGEIF formula is easy to use, but it has a limitation. It can only consider the month part and not the year. If you want to include the year in your average calculation, let’s move on to the next formula.
Average by Month and Year Using Filter and Average
To calculate the average by both month and year, we’ll use the FILTER and AVERAGE combo. Here’s how:
-
Modify your data to include values in multiple years.
-
Filter the range based on the month and year criteria using the following formula:
=average(filter($C$2:$C,month($B$2:$B)=E2,year($B$2:$B)=F2))Replace
E2andF2with the month and year you’re interested in.
Voila! You now have the average by month and year in Google Sheets.
Average by Month and Year Using Query in Google Sheets (Array Formula)
If you’re looking for an array formula to calculate the average by month and year, look no further than the QUERY function. Although some users find it intimidating, I assure you it’s simple to use and read.
To calculate the average using the QUERY function, use the following formula:
=query({B2:C},"Select month(Col1)+1,year(Col1),avg(Col2) where Col1 is not null group by month(Col1)+1,year(Col1) order by year(Col1)",0)You may notice that the month numbers returned by the QUERY formula range from 0 to 11. To understand why, read the formula explanation below the example.
Average by Month Name in Google Sheets (Query)
If you prefer using month names instead of numbers, you can achieve that with a simple QUERY trick. Here’s how:
- Convert the dates to the end of the month dates using the
EOMONTHfunction. - Group the data directly by the date column.
Use the following formula:
=Query({ArrayFormula(if(B2:B="",,eomonth(B2:B,0))),C2:C},"Select Col1,avg(Col2) where Col1 is not null group by Col1",0)Don’t forget to format the date values to the desired format using the FORMAT clause.
And there you have it! You now know how to calculate the average by month in Google Sheets. No more guessing or struggling with complicated formulas. Go ahead and impress your friends with your newfound skills!
Crawlan.com is a fantastic resource for all your Google Sheets needs. So be sure to check it out and explore more Google Sheets tips, tricks, and tutorials. Happy calculating!
