CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Badr VA
Badr VA

Reputation: 3

Google Sheets Query Clause: Calculate Item average sale price

I have a large table of 20 columns that contain 3 important columns of sales data by sales staff: Name, Qty, and Average sale price (designated Col1, Col2, Col3, respectively). To calculate average sale price of item using standard formula, I use =Sumproduct(C4:C8, D4:D8)/Sum(C4:C8), which gives a price of $13.52.

I tried to write a matching Query clause in D16 to calculate average sale price of item using: Query(B4:D8, "Select Sum(Col2*Col3)/Sum(Col2)").

However, this generates a "parsing" error" (see D18). Clearly, Summing product of 2 columns together then dividing by sum of one column requires a certain syntax. Are helper columns required? Any help with this one? Please note this has to be in a Query statement (Not SUMIF or Filter statements please). Here is the link to the sheet. https://docs.google.com/spreadsheets/d/e/2PACX-1vT_qwDiQxUF2wCSPS2ha_3fNgEPv85VNkzP99-MzcChpLL87oH0Pmbofy2-Zmj_o4w-b8JQ_TCZnsz8/pubhtml

Thank you all for your help. Badr

Upvotes: -3

Views: 52

Answers (2)

doubleunary
doubleunary

Reputation: 19145

You can do that with query() like this:

=query( 
  query(B3:D, "select Col2 * Col3, Col2", 1), 
  "select sum(Col1) / sum(Col2)", 
  1
)

...but it's unclear why you'd need to use query() when there are easier ways to get the same result, for example with average.weighted(), like this:

=average.weighted(D4:D8, C4:C8)

See average.weighted().

Upvotes: 1

PatrickdC
PatrickdC

Reputation: 2486

Add a Helper Column

Apparently, there is no documentation about the sum of products feature for Google Sheets QUERY function. In your case, a helper column added to the input of the QUERY function is a good workaround. Simply create a helper column for the product of Column C and Column D. The formula should look like this:

=QUERY({ARRAYFORMULA(C4:C8*D4:D8),C4:C8},"SELECT SUM(Col1)/SUM(Col2)")

Input:

Name Qty Avg. Price
John 4 13
Mary 6 14.2
Mary 9 13.6
Steve 3 13.3
Steve 4 13.1

Output:

quotient(sum sum )
13.53461538

To remove the header, you may add the INDEX at the beginning of the formula:

=INDEX(QUERY({ARRAYFORMULA(C4:C8*D4:D8),C4:C8},"SELECT SUM(Col1)/SUM(Col2)"),2)

Wherein the output will be a single cell with the value of 13.53461538.

NOTE: If you want, you may opt to file a feature request to add a sum of products feature for the Google Sheets Query function using this link.

Upvotes: 1

Related Questions