1

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

JMP
  • 4,417
  • 17
  • 30
  • 41
Jurre Robertus
  • 23
  • 2

1 Answers1

0

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

CMB
  • 4,950
  • 1
  • 4
  • 16
  • I get an ERROR... (Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 14. Actual: 1.) i have used the query you suggested: ={QUERY(DataFrame!A2:O,"select B, J where F='LinkedIn'", 0), transpose(split(REPT(AVERAGE(J2:J)&",",countifs(F2:F,"LinkedIn")),","))} – Jurre Robertus Mar 04 '21 at 11:25
  • If your sheet has not changed, can you try taking the average of column I instead of column J, as per my formula above? – CMB Mar 04 '21 at 15:49
  • The sheet has changed a bit, thats why i changed the I to a J. so J represents the values of I. – Jurre Robertus Mar 08 '21 at 08:06
  • I edited my answer. Since we are referencing from a different sheet every range should have DataFrame! before it. – CMB Mar 08 '21 at 14:17
  • If I answered your question, please click the accept button. By doing so, other people in the community, who may have the same concern as you, will know that theirs can be resolved. If the accept button is unavailable to you, feel free to tell me. https://stackoverflow.com/help/accepted-answer – CMB Mar 10 '21 at 14:20