5

Doing the following query = QUERY(A2:C, "select B, avg(C) group by B pivot A") returns a correct summary of my source data. However, there is blank line and a blank column in the result (see example). How to get rid of them? I can deal with the blank line using offset 1 but what about the column?

Also, using A2:C11 to specify the data, there are no blank lines or columns. But then when data gets added at the end of the source range, it doesn't get included in the query result (obviously). How to prevent the blank line and column?

jakub
  • 4,774
  • 4
  • 29
  • 46

2 Answers2

17

You probably have some blank rows in your source range. Try:

= QUERY(A2:C, "select B, avg(C) where C is not null group by B pivot A")
JPV
  • 26,499
  • 4
  • 33
  • 48
  • Nailed it. Why does `= QUERY(A2:C, "select B where B is not null, avg(C) group by B pivot A")` not work? I mean, it should be whichever of those three columns, right? – jakub Feb 18 '15 at 18:11
  • 2
    "..is not null" can only be used in columns with numeric values. In case you have textual values you should have: "where B <>'' ". – JPV Feb 18 '15 at 19:22
  • Ahhh! I thought my head would explode as I was trying to find out the difference. By extension, `where B <>''` should not work with numeric values, right? – jakub Feb 19 '15 at 11:23
  • Correct. And "where B <>'' equals "where B != '' ". – JPV Feb 19 '15 at 12:27
3

If you use WHERE (B IS NOT NULL AND B<>'') you'll exclude also blank/empty values

Enrico Di Cesare
  • 41
  • 1