-1

enter image description here

In column D, for each student I'd like to find average of percentage of all the tests they took.

For Student 1, I need to take percentage of each test by 8/10, 25/25, 35/50 and find average in D6, etc.

I tried =average(($E6:$E100)/($E3:$AB3)) but it did not work. How can I get this?

shin
  • 31,901
  • 69
  • 184
  • 271
  • so you want average per row of columns E:AB? – player0 Nov 12 '19 at 23:53
  • You have referred to "Column D". Would you please be more specific - are you referring to Cell D5 - "the class average", or an average for each student - that is, a value in each cell in Column D, or both, or something else. In any event, disregarding the syntax of a Google formula, would you please advise the values that you expect from a successful outcome. – Tedinoz Nov 13 '19 at 00:44
  • Updated. I hope it makes sense. – shin Nov 13 '19 at 01:07

3 Answers3

1

try:

=ARRAYFORMULA(QUERY(TRANSPOSE(QUERY(TRANSPOSE(E6:Z), 
 "select "&TEXTJOIN(",", 1, IF(LEN(B6:B), 
 "avg(Col"&ROW(B6:B)-ROW(B6)+1&")", )))&""),
 "select Col2")*1)

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • I got the same result with `average(E7:G7)`, etc. It's beyond me, but presumably you could turn that into an array formula, and it would copy down automatically. My only concern is, what does it actually mean? – Tedinoz Nov 13 '19 at 00:32
  • It did not work for me. I need to find the percentage first. For st2, by 7/10, 20/25, 40/50 and finding the average. – shin Nov 13 '19 at 01:13
0

This would be the class average, to appear in cell D5 =(sum(E7:G))/count(D7:D)

Based on the test data, the class average is 67.75.

Tedinoz
  • 5,911
  • 3
  • 25
  • 35
0

I think just for 10/10, 25/25, 35/50 you can try with:

=Average(Arrayformula({e6:g6}/{e3:g3}))
user11982798
  • 1,878
  • 1
  • 6
  • 8