How to Calculate Average by Month in Google Sheets
To calculate the average by month in Google Sheets, you can use the AVERAGEIFS function. This function allows you to calculate the average of a range based on multiple criteria. In this case, the criteria will be the month.
Here's a step-by-step guide on how to calculate the average by month in Google Sheets:
- Organize your data in columns, with one column for dates and another for the values you want to average.
- In an empty cell, type the following formula:
=AVERAGEIFS(values_range, dates_range, ">="&date(year, month, 1), dates_range, "<"&date(year, month+1, 1))Replace values_range with the range of cells containing the values you want to average, dates_range with the range of cells containing the dates, year with the year you want to calculate the average for, and month with the month (as a number) you want to calculate the average for.
For example, if your dates are in column A (from A2 to A100), the values are in column B (from B2 to B100), and you want to calculate the average for January 2022, the formula would look like this:
=AVERAGEIFS(B2:B100, A2:A100, ">=2022-01-01", A2:A100, "<2022-02-01")- Press Enter to get the average for the specified month.
- If you want to calculate the average for other months, simply change the
yearandmonthvalues in the formula.
Example
Let's assume you have the following data in your Google Sheet:
| Date | Sales |
|---|---|
| 2022-01-01 | 100 |
| 2022-01-02 | 150 |
| 2022-01-03 | 200 |
| 2022-02-01 | 300 |
| 2022-02-02 | 400 |
To calculate the average sales for January 2022, you can use the following formula:
=AVERAGEIFS(B2:B6, A2:A6, ">=2022-01-01", A2:A6, "<2022-02-01")The result will be 150, which is the average of the sales values for January 2022.
To calculate the average sales for February 2022, change the formula to:
=AVERAGEIFS(B2:B6, A2:A6, ">=2022-02-01", A2:A6, "<2022-03-01")The result will be 350, which is the average of the sales values for February 2022.