Google Query Average

Check google's documentation on google sheets functions.

Introduction

Query is one of the most powerful functions in google sheets. Let's begin with the basics. Say we have data in the range B5:Z being, all the way down with repeating dates as well as being updated from every 6 hours(designated by quarter no.) just like this:

source

This is how our final formula would look like:

Entire Formula

=arrayformula(if(query(QUERY(arrayformula(IMPORTRANGE("https://docs.google.com/spreadsheets/d/14xh2SsMdK2rOpAwSn5SKtPtTzQjxty524HafMtR5J2A/edit#gid=754339136","Data!B6:AA")+0), "SELECT Col1, AVG(Col3), AVG(Col4), AVG(Col5), AVG(Col6), AVG(Col7), AVG(Col8), AVG(Col9), AVG(Col10), AVG(Col11), AVG(Col12), AVG(Col13), AVG(Col14), AVG(Col15), AVG(Col16), AVG(Col17), AVG(Col18), AVG(Col19), AVG(Col20), AVG(Col21), AVG(Col22), AVG(Col23), AVG(Col24), AVG(Col25), AVG(Col26) WHERE NOT Col1 = '' GROUP BY Col1 LABEL AVG(Col3) '', AVG(Col4) '', AVG(Col5) '', AVG(Col6) '', AVG(Col7) '', AVG(Col8) '', AVG(Col9) '', AVG(Col10) '', AVG(Col11) '', AVG(Col12) '', AVG(Col13) '', AVG(Col14) '', AVG(Col15) '', AVG(Col16) '', AVG(Col17) '', AVG(Col18) '', AVG(Col19) '', AVG(Col20) '', AVG(Col21) '', AVG(Col22) '', AVG(Col23) '', AVG(Col24) '', AVG(Col25) '', AVG(Col26) ''",0), "SELECT * OFFSET 1", 0)=0,"",query(QUERY(arrayformula(IMPORTRANGE("https://docs.google.com/spreadsheets/d/14xh2SsMdK2rOpAwSn5SKtPtTzQjxty524HafMtR5J2A/edit#gid=754339136","Data!B6:AA")+0), "SELECT Col1, AVG(Col3), AVG(Col4), AVG(Col5), AVG(Col6), AVG(Col7), AVG(Col8), AVG(Col9), AVG(Col10), AVG(Col11), AVG(Col12), AVG(Col13), AVG(Col14), AVG(Col15), AVG(Col16), AVG(Col17), AVG(Col18), AVG(Col19), AVG(Col20), AVG(Col21), AVG(Col22), AVG(Col23), AVG(Col24), AVG(Col25), AVG(Col26) WHERE NOT Col1 = '' GROUP BY Col1 LABEL AVG(Col3) '', AVG(Col4) '', AVG(Col5) '', AVG(Col6) '', AVG(Col7) '', AVG(Col8) '', AVG(Col9) '', AVG(Col10) '', AVG(Col11) '', AVG(Col12) '', AVG(Col13) '', AVG(Col14) '', AVG(Col15) '', AVG(Col16) '', AVG(Col17) '', AVG(Col18) '', AVG(Col19) '', AVG(Col20) '', AVG(Col21) '', AVG(Col22) '', AVG(Col23) '', AVG(Col24) '', AVG(Col25) '', AVG(Col26) ''",0), "SELECT * OFFSET 1", 0)))

Now we want to call all these data to a different google sheet file and get its average values per day. Let's buildup our formula slowly.

First we call everything by using (1) =query("range from our source","select * ",0) and from here we slowly build up our formula.

(a.1) "select * " - select everything form our range

(b.1) 0 - number of headers

(c.1) "range from our source" - data that we need

We would like to have our formula as short as possible. Let's include all data starting from column B to Z of our source even though we won't be needing our column C(Quarter). Replace "range from our source" with this formula (2) IMPORTRANGE("https://docs.google.c........39136","Data!B6:AA")

(a.2) "https://docs.google.c........39136" - link of our google sheet source

(b.2) "Data!B6:AA" - name of sheet tab and corresponding cells separated by "!"

Add "0" to Every Cell

Use arrayformula to (2) in order to add "0" to our 2d array(every cell), so there won't be left any blank for the purpose of avoiding error during query with average. There must be atleast one number in a cell to avoid error when performing average calculation. Then use query to select all data with average, QUERY(arrayformula(IMPORTRANGE("https://do....GROUP BY Col1 LABEL AVG(Col3) '', AVG(Col4) ''..., AVG(Col26) ''",0) u have to add label by ''(blank space), so we can remove single row for its label. There is another blank space that we need to remove. Whenever we call data ending wit letters same as B5:Z it also count blank spaces as unique and interpret it as the first date because its value is zero. Date has a floating value starting point 1/1/1900 equal to 0 and that's why we need to call another query that ends with "SELECT * OFFSET 1", 0) in order to remove first row of data. Our formula now looks like;

(3) =query(QUERY(arrayformula(IMPORTRANGE("https://docs.google.com/spreadsheets/d/14xh2SsMdK2rOpAwSn5SKtPtTzQjxty524HafMtR5J2A/edit#gid=754339136","Data!B6:AA")+0), "SELECT Col1, AVG(Col3), AVG(Col4), AVG(Col5), AVG(Col6), AVG(Col7), AVG(Col8), AVG(Col9), AVG(Col10), AVG(Col11), AVG(Col12), AVG(Col13), AVG(Col14), AVG(Col15), AVG(Col16), AVG(Col17), AVG(Col18), AVG(Col19), AVG(Col20), AVG(Col21), AVG(Col22), AVG(Col23), AVG(Col24), AVG(Col25), AVG(Col26) WHERE NOT Col1 = '' GROUP BY Col1 LABEL AVG(Col3) '', AVG(Col4) '', AVG(Col5) '', AVG(Col6) '', AVG(Col7) '', AVG(Col8) '', AVG(Col9) '', AVG(Col10) '', AVG(Col11) '', AVG(Col12) '', AVG(Col13) '', AVG(Col14) '', AVG(Col15) '', AVG(Col16) '', AVG(Col17) '', AVG(Col18) '', AVG(Col19) '', AVG(Col20) '', AVG(Col21) '', AVG(Col22) '', AVG(Col23) '', AVG(Col24) '', AVG(Col25) '', AVG(Col26) ''",0), "SELECT * OFFSET 1", 0)

Include Function to Remove "0"

Again we need to remove zero because it might be interpreted as a real value., when it might be that lab test wasn't done around that time and its value must be blank instead of 0. So we need to use if statements and another array formula to perform whole array or it would give as a value for a single cell. The formula must be arrayformula(if((3)=0"","",(3))).

final

It's finally done!.

Message me if you find anything wrong with this content. Any comment would mean so much to me. It will help me grow and give you a better content.