Search code examples
google-sheetsaveragegoogle-sheets-formulagoogle-sheets-queryarithmetic-expressions

Google sheets query functions: how to do arithmetic on columns with non-constants, i.e. dividing not by a constant but by another cell?

I understand how to do arithmetic on a column using a query function as long as I'm using a fixed constant. However, if I try to do the same using a cell reference instead of a constant, I get an error.

I've tried making the cell a named range and referring to that name hoping it would act like a constant but I still get errors. There must be a way to do this. Here are examples of things that work:

=QUERY(FebMarket,"SELECT (C/5)") //This divides column C by 5

=QUERY(FebMarket,"SELECT AVG(C)") //This gives me the average of column C

=QUERY(FebMarket,"SELECT AVG(C) LABEL AVG(C) ''") //average of col C without a header

However, if I do any of the following I get an error:

=QUERY(FebMarket,"SELECT C/(AVG(C) LABEL AVG(C) '')")

=QUERY(FebMarket,"SELECT C/(AVG(C))")

=QUERY(FebMarket,"SELECT C/AVG(C)")

=QUERY(FebMarket,"SELECT C/avgC") // where 'avgC' is a named range given to a cell where I calculated the average of column C separately

Solution 
  • =QUERY(QUERY(C1:C,
 "select C / "&AVERAGE(C1:C), 0), 
 "select Col1 
  where Col1 is not null
  label Col1 '' ", 0)