Yogi Anand, D.Eng, P.E. Google Spreadsheet www.energyefficientbuild.com
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
A__________50
B__________100
A__________75
A__________80
B__________125
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
A__3
B__2
And =QUERY(DATA!B2:F ; "Select B, Sum(E) Group by B Order by B ") to get
A__205
B__225
then using the above data to calculate average number of parts made.
A__68.33
B__112.5
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?
https://docs.google.com/
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
col B_______Col E
Partname ___No parts made
A__________50
B__________100
A__________75
A__________80
B__________125
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
A__3
B__2
And =QUERY(DATA!B2:F ; "Select B, Sum(E) Group by B Order by B ") to get
A__205
B__225
then using the above data to calculate average number of parts made.
A__68.33
B__112.5
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?
https://docs.google.com/
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
No comments:
Post a Comment