How to calculate average of a column and then include in a select query Google Sheets, limit results and filter the results

1
votes

ive been working on a social media dashboard in google sheets. the first picture is how i ordered my data. I am from Holland so the description is in Dutch. The second picture is the query i got from stackoverflow :) the third picture is the resulting table from the query shown in picture 2. i have two problems and i am new to working with google sheets or excel so i am sorry for asking simple questions.

problem 1 I want to filter the social media channel (column F) so i used the following query:

=QUERY(QUERY(DataFrame!A2:O, "Select B, I where F = 'LinkedIn' " &AVERAGE(I2:I25)), "offset 1", 0)

this results in #VALUE!. i dont know what to do

problem 2 the avg column just keeps on going. How do i limit it so it doesn't just keeps going. ive tried using a limit statement in the query

this is how i ordered my data (im dutch so it is in dutch)

this is my query

this is the result of the query

1
google-sheetsgoogle-sheets-formula

1 Answers

0
votes

Explanation:

Your first formula is returning VALUE! because it is trying to merge tables with different number of columns.

Your second formula is returning an excess number of repeating values because the dataset A2:O includes blank rows.

Solution:

You can use simple array notation to merge a query formula and a complex formula meant to repeat a single value across columns:

With LinkedIn filter:

={QUERY(DataFrame!A2:O,"select B,I where F='LinkedIn'", 0),transpose(split(REPT(AVERAGE(DataFrame!I2:I)&",",countifs(DataFrame!F2:F,"LinkedIn")),","))}

Without filter:

={QUERY(DataFrame!A2:O,"select B,I where B<>''", 0),transpose(split(REPT(AVERAGE(DataFrame!I2:I)&",",count(DataFrame!F2:F)),","))}

Sample Output:

enter image description here

enter image description here