Thursday, January 19, 2012

yogi_Query Data For Specified Condition And Compute Count Sum And Average

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                  

user jimbo66 said:
How to calculate count, sum and average for an array
in this sample spreadsheet
col B_______Col E
Partname ___No parts made
I am using the following function to count the number of times each part name occurs in column B
=QUERY(DATA!B2:F ; "Select B, count(E) Group by B Order by B ") to get
And =QUERY(DATA!B2:F ; "Select B, Sum(E) Group by B Order by B ") to get
then using the above data to calculate average number of parts made.
I want to get all three answers( count, sum, average) for each part name sorted by part name.
 Can I do this all in one function?
If I want to do this and only select where part color is red. Colors are listed in column C.
How do I do this?
The data sheet is generated from a form.  I have stripped the data to make a sample data sheet.
On the report page the three items in the total column that are red are incorrect because I only want to total, count and average the rows in the data sheet where the operation is "in" and skip the rows where the operation is "out".
The columns generated by the query commands A-B and F-G will keep growing as different parts are made but I have to keep adding the compute functions for columns C and D.
If there is a way to compute sum, count and average in one command, the report will continuously be generated
with the correct number of rows
following is a solution to the problem