Google Sheets – Calculate Average on Variable Number of Non-Adjacent Columns

google sheetsgoogle-sheets-queryvlookup

A Google Sheet is being used to insert markings for students tests.

When a single test is done by students, teacher assigns multiple grades for it. For instance, one marking for writing, one for comprehension, etc.

The sheet should finally build columns containing an average for all the markings assigned within the same date.

enter image description here

For instance, in the above sheet (link here), columns with markings given on December 16th (cols B,G,M,R,V) should be averaged in column AE.

How could I use VLOOKUP or MATCH to lookup all markings in a given row, where the date in row# 3 is "16-12"?? Do I need QUERY/`IMPORTRANGE, or maybe a full Google Apps Script to achieve this?

Basically I need a way to calculate averages for a filtered range with a variable number of columns.

EDIT

Since marikamitsos solution, now the problem seems to be managing rows with no values, as this will throw an error….

EDIT 2

Solved here

Best Answer

You can use the following formula in cell AE4

=ARRAYFORMULA(QUERY(TRANSPOSE(QUERY(TRANSPOSE(FILTER(B4:Z,B3:Z3=DATE(2019,12,16))), 
 "select "&TEXTJOIN(",", 1, IF(LEN(A4:A), 
 "avg(Col"&ROW(A4:A)-ROW(A4)+1&")", )))&""),
 "select Col2")*1)

(If this is what you are looking for, I will update with image and more info as well)

Related Topic