Skip to main content
Web Applications

Return to Question

added 4 characters in body; edited tags
Source Link
Rubén - Volunteer Moderator - ♦
Rubén - Volunteer Moderator - ♦
  • 46.1k
  • 18
  • 100
  • 297

I have the following formula: =query({'6th LA'!A2:G66;'6th SS'!A2:G66;'6th Math'!A2:G66;'6th Sci'!A2:G66;'7th LA'!A2:G93;'7th Sci'!A2:G93;'7th SS'!A2:G93;'7th Math'!A2:G93;'8th LA'!A2:G80;'8th SS'!A2:G80;'8th Math'!A2:G80;'8th Sci'!A2:G80;'8th Keyboarding'!A2:G80;PE!A2:G237;Drama!A2:G237;Art!A2:G237;Music!A2:G237;'Monica Robison'!A2:G237},"select Col1, Avg(Col7) where Col1 != '' group by Col1 label AVG(Col7) 'Overall Concern'")=query({'6th LA'!A2:G66;'6th SS'!A2:G66;'6th Math'!A2:G66;'6th Sci'!A2:G66;'7th LA'!A2:G93;'7th Sci'!A2:G93;'7th SS'!A2:G93;'7th Math'!A2:G93;'8th LA'!A2:G80;'8th SS'!A2:G80;'8th Math'!A2:G80;'8th Sci'!A2:G80;'8th Keyboarding'!A2:G80;PE!A2:G237;Drama!A2:G237;Art!A2:G237;Music!A2:G237;'Monica Robison'!A2:G237},"select Col1, Avg(Col7) where Col1 != '' group by Col1 label AVG(Col7) 'Overall Concern'")

It works great, but when it averages column 7 across all the sheets according to the student name, it is dividing by all the sheets the student is in and not taking out the Null values. So for example, I have 2,2,3,3,5,0,0,42,2,3,3,5,0,0,4. The average should be 3.1 if 0s are not counted, but it is coming back as 2 because it is including the 0s.

How do I fix this?

I have the following formula: =query({'6th LA'!A2:G66;'6th SS'!A2:G66;'6th Math'!A2:G66;'6th Sci'!A2:G66;'7th LA'!A2:G93;'7th Sci'!A2:G93;'7th SS'!A2:G93;'7th Math'!A2:G93;'8th LA'!A2:G80;'8th SS'!A2:G80;'8th Math'!A2:G80;'8th Sci'!A2:G80;'8th Keyboarding'!A2:G80;PE!A2:G237;Drama!A2:G237;Art!A2:G237;Music!A2:G237;'Monica Robison'!A2:G237},"select Col1, Avg(Col7) where Col1 != '' group by Col1 label AVG(Col7) 'Overall Concern'")

It works great, but when it averages column 7 across all the sheets according to the student name, it is dividing by all the sheets the student is in and not taking out the Null values. So for example, I have 2,2,3,3,5,0,0,4. The average should be 3.1 if 0s are not counted, but it is coming back as 2 because it is including the 0s.

How do I fix this?

I have the following formula: =query({'6th LA'!A2:G66;'6th SS'!A2:G66;'6th Math'!A2:G66;'6th Sci'!A2:G66;'7th LA'!A2:G93;'7th Sci'!A2:G93;'7th SS'!A2:G93;'7th Math'!A2:G93;'8th LA'!A2:G80;'8th SS'!A2:G80;'8th Math'!A2:G80;'8th Sci'!A2:G80;'8th Keyboarding'!A2:G80;PE!A2:G237;Drama!A2:G237;Art!A2:G237;Music!A2:G237;'Monica Robison'!A2:G237},"select Col1, Avg(Col7) where Col1 != '' group by Col1 label AVG(Col7) 'Overall Concern'")

It works great, but when it averages column 7 across all the sheets according to the student name, it is dividing by all the sheets the student is in and not taking out the Null values. So for example, I have 2,2,3,3,5,0,0,4. The average should be 3.1 if 0s are not counted, but it is coming back as 2 because it is including the 0s.

How do I fix this?

Source Link
Margie Stebbeds
Margie Stebbeds
  • 3
  • 1

importing data from several sheets and average without including 0s

I have the following formula: =query({'6th LA'!A2:G66;'6th SS'!A2:G66;'6th Math'!A2:G66;'6th Sci'!A2:G66;'7th LA'!A2:G93;'7th Sci'!A2:G93;'7th SS'!A2:G93;'7th Math'!A2:G93;'8th LA'!A2:G80;'8th SS'!A2:G80;'8th Math'!A2:G80;'8th Sci'!A2:G80;'8th Keyboarding'!A2:G80;PE!A2:G237;Drama!A2:G237;Art!A2:G237;Music!A2:G237;'Monica Robison'!A2:G237},"select Col1, Avg(Col7) where Col1 != '' group by Col1 label AVG(Col7) 'Overall Concern'")

It works great, but when it averages column 7 across all the sheets according to the student name, it is dividing by all the sheets the student is in and not taking out the Null values. So for example, I have 2,2,3,3,5,0,0,4. The average should be 3.1 if 0s are not counted, but it is coming back as 2 because it is including the 0s.

How do I fix this?