Average across sheets Google Sheets

by John Sumner   Last Updated November 15, 2017 03:03 AM

So lets say I have sheet1

a | 4
b | 5
c | 33
d | 55

and sheet2

a | 1
b | 2
c | 3
e | 2

And on sheet3 I want to get it in this kind of format.

name | average | number of times referenced?
a         2.5              2
b         3.5              2 
c         18               2  
d         55               1
e         2                1

How would I go about doing this?

Tags : google-sheets


Answers 1


Please try something like:

=query({Sheet1!A1:B10;Sheet2!A1:B10},"select Col1, Avg(Col2), count(Col1) where Col1 is not NULL group by Col1 label Col1 'name', count(Col1) 'number of times referenced?', Avg(Col2) 'average'")

Adjust ranges to suit those you did not mention.

QUERY

For your sample not necessary but you might want to insert order by Col1 asc before label.

pnuts
pnuts
November 15, 2017 02:28 AM

Related Questions



How to calculate and chart cumulative flex time?

Updated November 09, 2017 14:03 PM

Create chart from single column

Updated November 12, 2017 13:03 PM