How to Calculate Average by Quarter in Google Sheets

GiGiKing·March 12, 2024·0 comments

If you want to calculate the quarterly average in Google Sheets, you have three approaches to choose from: QUERY, Pivot Table, and AVERAGEIFS. Each method has its own advantages and requirements. Let’s dive into each of them and see how you can calculate the average by quarter using these functions.

Calculating Average by Quarter Using the QUERY Function

The QUERY function allows you to calculate the average, grouped by quarter, without the need for a helper column. To use this method, follow these steps:

  1. Enter your sample data, where column A contains sales dates and column B contains sales amounts. [^image1]

  2. To calculate the average by quarter for a specific year, enter the following formula:

=QUERY(A1:B, "SELECT QUARTER(A), AVG(B) WHERE YEAR(A) = 2023 GROUP BY QUARTER(A)", 1)

[^image2]

The formula follows the syntax of the QUERY function:

QUERY(data, query, [headers])

Where:

  • data: A1:B
  • query: “SELECT QUARTER(A), AVG(B) WHERE YEAR(A) = 2023 GROUP BY QUARTER(A)”
    • SELECT QUARTER(A), AVG(B): Selects the quarter of the date in column A and calculates the average of corresponding values in column B.
    • WHERE YEAR(A) = 2023: Filters the data to include only rows where the year in column A is 2023.
    • GROUP BY QUARTER(A): The grouping is necessary for obtaining quarterly averages. If you just use “SELECT AVG(B)”, it returns the average of values in column B overall. However, since you selected QUARTER(A), grouping by quarter is essential to get the average for each quarter separately.
  • headers: 1

Adjustments in the QUERY Formula for Calculating Average by Year-Quarter

To calculate the average by year and quarter using the provided QUERY formula, make the following changes:

  1. Replace the filter condition WHERE YEAR(A) = 2023 with WHERE A IS NOT NULL.
  2. Replace SELECT QUARTER(A) with SELECT YEAR(A), QUARTER(A).
  3. Replace GROUP BY QUARTER(A) with GROUP BY YEAR(A), QUARTER(A).

Adjusted Formula:

=QUERY(A1:B, "SELECT YEAR(A), QUARTER(A), AVG(B) WHERE A IS NOT NULL GROUP BY YEAR(A), QUARTER(A)", 1)

[^image3]

Calculating Average by Quarter Using the Pivot Table

The Pivot Table method allows you to calculate the average, grouped by quarter, without the need for a helper column. To use this method, follow these steps:

  1. Click on “Insert” > “Pivot Table.”
  2. Enter “A:B” in the field below “Data range.”
  3. Select “Existing Sheet” and enter “D1” in the field below.
  4. Click “Create.”
  5. In the Pivot Table editor panel, drag and drop the ‘Date’ field below Rows.
  6. Drag and drop the ‘Sales’ field below Values and select “AVERAGE” under “Summarize by.”
  7. Drag and drop the ‘Date’ field below Filters. Under “Status,” click the drop-down, select “Filter by conditions,” then choose “Custom formula is,” and enter the following formula: =YEAR(Date)=2023.
  8. Click “OK.”
  9. Right-click on any date in column D in the Pivot Table report. From the context menu, select “Create pivot date group” and then choose “Quarter.”

Adjustments in the Pivot Table Editor Panel for Calculating Average by Year-Quarter

To calculate the average by year and quarter in the Pivot Table, make the following adjustments:

  1. Right-click on Q1, Q2, Q3, or Q4 in column D, then select “Create pivot date group” and choose “Year-Quarter.”
  2. Click on the pencil icon at the bottom left corner of the Pivot Table to open the sidebar editor panel. Scroll down to the bottom, and within the FILTERS section, replace the earlier custom formula with the following one: =DATEVALUE(Date)>0.

[^image4]

Calculating Average by Quarter Using the AVERAGEIFS Function

The AVERAGEIFS function requires a helper column containing quarters. Here’s how to use this method:

  1. Use the following formula in cell C2 to assign quarters to the dates:
=ArrayFormula(IFERROR("Q"&INT((MONTH(DATEVALUE(A2:A))+2)/3)))

  1. In the above formula, the INT((MONTH(DATEVALUE(A2:A))+2)/3) part converts the month to a quarter number (1, 2, 3, or 4). The DATEVALUE is used in the formula to handle blank cells, ensuring it returns an error for them.

  2. Prepend “Q” to the numbers obtained above using the ampersand. The IFERROR function is used to handle any errors that might be returned by the formula, ensuring a clean output.

  3. Use the following formula in cell E2 to get unique quarters sorted in ascending order:

=SORT(UNIQUE(C2:C))
  1. Use the following AVERAGEIFS formula in cell F2 to calculate the quarterly averages:
=ArrayFormula(AVERAGEIFS($B$2:$B, $C$2:$C, E2, YEAR($A$2:$A), 2023))
  1. Click and drag the blue square (fill handle) in the lower-right corner of cell F2 to fill the remaining cells with quarterly averages.

Adjustments in the Formulas for Calculating Average by Year-Quarter

To calculate the average by year and quarter, make the following adjustments:

  1. Replace the formula in cell C2 with the following one, which prepends the year to the quarter label:
=ArrayFormula(IFERROR(YEAR(DATEVALUE(A2:A))&"-Q"&INT((MONTH(DATEVALUE(A2:A))+2)/3)))
  1. Replace the formula in cell F2 with the following one:
=AVERAGEIFS($B$2:$B, $C$2:$C, E2)

[^image5]

Selecting the Best Option from the Three Choices

Among the three methods, the Pivot Table stands out as the most flexible option. It allows easy switching between quarter, year-quarter, month, year, and year-month summary reports. If you’re a beginner, the Pivot Table is the recommended choice. If you want to learn functions, opt for AVERAGEIFS. Personally, I prefer QUERY for its simplicity, but if customization is essential, consider exploring the LABEL clause.

Resources

If you’re interested in exploring more about quarterly-based calculations beyond calculating averages by quarter, check out these guides:

  1. Group Dates in Pivot Table in Google Sheets (Month, Quarter, and Year)
  2. Formula to Group Dates by Quarter in Google Sheets
  3. Query to Create Daily/Weekly/Monthly/Quarterly/Yearly Report Summary in Google Sheets
  4. Extract Quarter from a Date in Google Sheets – Formula Options
  5. Query Quarter Function in Non-Calendar Fiscal Year Data (Google Sheets)
  6. Convert Dates To Fiscal Quarters in Google Sheets
  7. Current Quarter and Previous Quarter Calculations in Google Sheets

Remember, if you need more assistance or want to dive deeper into SEO and digital marketing topics, visit Crawlan.com. Happy sheeting!

GiGiKing

Related posts