CODEWITHSUNDEEP
google-sheetsgoogle-docs
maxm
maxm

Reputation: 5371

Google Sheets: how to find the average of values in one column grouped by another column's values?

Let's say I have a Google Sheet with the following data:

Column1|Column2
A|1
A|2
A|3
A|4
B|1
B|2

And I want to find the mean of the values in Column2 grouped by the values of Column1, so output liked the following:

A|2.5
B|1.5

Should I be looking at query? Or using pivot tables?

Upvotes: 7

Views: 6367

Answers (2)

Chris Hick
Chris Hick

Reputation: 3094

This formula would also give you the example output:

=QUERY(A:B,"select A,avg(B) where A <> '' group by A label A '', avg(B) ''")

Upvotes: 5

Dijkgraaf
Dijkgraaf

Reputation: 11527

By a Pivot Table

Range: Sheet1!A1:B7

Rows: 
   Group by: Column1

Columns: 
   Group by: Column2

Calculated Field: 
   Forumula: =AVERAGE=(Column2)
   Summarise by: Custom

Results

            1   2   3   4   Grand Total
A           1   2   3   4   2.5
B           1   2           1.5
Grand Total 1   2   3   4   2.166666667

Note: I could not work out how to change the text Grand Total to Average

Upvotes: 3

Related Questions