The title of this post may be a little bit confusing if you are not familiar with the grouping of data. So first let me explain what I am trying to say with the above title Group and Average Unique Column in Google Sheets. Let me begin with Average, the main topic of this post.
Calculate Average in Google Sheets:
As you may know, when we talk about spreadsheets, the Average is the Sum of a column divided by the count of the same column or another column with the same array size in the data set.
While calculating the average, we are not taking into consideration whether there are multiple occurrences of the same value in our data set.
Calculate Average of Unique Column in Google Sheets:
If the average is the sum divided by count, unique column average is the sum divided by Count unique.
You May Like: Learn Google Sheets all Count related functions
Let me make it clearer to you.
The total count of records in this data set is 7 and the sum of column C is 80. So the normal average is;
80/7 = 11.43
In Google Sheets, there is no need to count and sum to find an average. You can calculate the average using the Average formula in Google Sheets.
=average(C2:C8)
There is no average unique in Google Sheets. So to find average unique, first, you need to find the count of unique columns as below.
=countunique(A2:B8)
Must Check: How to Use All Google Sheets Count Functions [All 8 Count Functions]
This would return 5 as there are only five unique records in this data set. Because I’ve considered columns A and B. If I only choose column A, then there would be only 2 unique values.
We already know the sum of column C is 80. You can find the average of unique columns as below.
=sum(C2:C8)/countunique(A2:B8)
It would return the value 16 as average as it’s equal to 80/5. Now let’s see what is group wise average in Google Sheets.
How to Group and Average Unique Column in Google Sheets
Just like the above example, in grouping also there are two types of averages. Normal group wise average and average based on unique column values. For both these, we can use Query function as it’s the best in the grouping of data.
Similar: How to Group a Column Based on First Few Characters in Google Sheets
Calculate Normal Group Wise Average in Google Sheets
It’s like first sum column C if the value in Column A is “Cary”. If you sum so, you will get the total value as 50.
Divide this value by the total occurrences of the name “Cary” which is 4. So50/4 = 12.5.
That’s the output you can see on the above image. Just follow this steps for the other person too.
But you can use a Query formula as below to automate the grouping of average in Google Sheets.
The Formula to Calculate the Normal Group Wise Average
This Query formula uses the Avg Aggregate function in Query.
=query({A2:A,C2:C},"Select Col1, Avg (Col2) where Col1<>'' group by Col1 label Avg(Col2)''")
Here Col1 is Column A and Col2 is Column C as I have skipped column B in the Query formula range.
The reason, we have nothing to do with unique values here as it’s a normal group wise average. We only require Column A and C.
To understand the above formula you may first understand how to use Query in Google Sheets.
Tips: Learn Google Sheets Query
I have used the group clause and Avg aggregate function in Query to get the group-wise average.
Then how to Group and Average Unique Column in Google Sheets?
Calculate Unique Group Wise Average in Google Sheets
This Query formula uses the Sum and Count Aggregate functions in Query.
For this, first, you should make a group-wise sum and then a unique group wise count.
That means there are two groupings (formulas) involved. So we can use two query formulas as below and divide the outputs.
Formula 1:
This formula is similar to the above average formula. Here I’ve used the aggregate function SUM, not AVG.
=query(ArrayFormula({A2:A,C2:C}),"Select Col1, Sum (Col2) where Col1<>'' group by Col1 label Sum(Col2)''")
See the result.
Now we want to do a group-wise unique count.
=query(unique(A2:B),"Select Col1, Count (Col1) where Col1<>'' group by Col1 label Count(Col1) ''")
This formula is also similar to the above one. But here instead of Sum, I’ve used the Count aggregate function in Query.
Additionally, you can see the use of Unique in the Query range. Also, the Query range is different. I’ve included the column A and B here to find unique values.
Now you only need to divide these values from the above corresponding summed values.
We can’t do this until we remove the names associated with the values. You can remove that easily. How?
Just remove theCol1, in the “Select” clauses of both the above formulas. Then you will only be left with summed values in one formula and the count unique values in another.
Now you can divide these values and the final formula would be like this.
=ArrayFormula(query({A2:A,C2:C},"Select Sum (Col2) where Col1<>'' group by Col1 label Sum(Col2)''")/query(unique(A2:B),"Select Count (Col1) where Col1<>'' group by Col1 label Count(Col1) ''"))
Additionally, I have used the array formula as we used the division operator (forward slash) between the two query formulas.
Here is the group-wise unique column average result.
We got the unique column average. But does this justify my post title saying Group and Average Unique Column in Google Sheets? Certainly not! To complete the above process, you should place the names before the output column. That makes the grouping perfect.
Just add another piece of formula in front of the above formula to insert the unique names before the output values.
Compare the below formula with the just above formula. So you can understand what I have done to get the name in front of the average.
={unique(sort(A2:A8)),ArrayFormula(query({A2:A,C2:C},"Select Sum (Col2) where Col1<>'' group by Col1 label Sum(Col2)''")/query(unique(A2:B),"Select Count (Col1) where Col1<>'' group by Col1 label Count(Col1) ''"))}
See the final result.
Conclusion
To Group and Average Unique Column in Google Sheets, you should know the basic use of Query function. Otherwise, you may not be able to understand how the formula develops.
There are ample Query related tutorials on this page. Find time to try few of them.
