1

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

Improve this question
3
  • You provided a copy of your spreadsheet. Thank you. Would you please edit your spreadsheet to include an example of a successful outcome.
    – Tedinoz
    Jan 12, 2020 at 9:02
  • There's no outcome at the moment... My only guess is to write a Google apps script to do the work. I was just wondering if I could achieve this with formulas, before reinventing the wheel
    – Riccardo
    Jan 12, 2020 at 11:18
  • "There's no outcome at the moment..." I think I might have confused you. When we ask for an example of a successful outcome, we don't expect you to write the code to do it (I mean, that's why you asked the question), but rather i) the layout of the required outcome, and (probably calculated manually) the actual answers so that we know what results we are trying to achieve.
    – Tedinoz
    Jan 12, 2020 at 22:32

1 Answer 1

Reset to default
1

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)

Improve this answer
10
  • 1
    Hats off mate! I feel happy and disappointed at the same time. Disappointed because I could have never achieved that formula by my self (well, maybe if working full time on sheet formulas), happy because I can see something brilliant as your work! Happiness exceeds by far disappointment. Thanks again for your help :-)
    – Riccardo
    Jan 12, 2020 at 16:37
  • are you on linkedin? Would like to add you to my network
    – Riccardo
    Jan 12, 2020 at 16:50
  • BTW, why if the date in AE3 is change to something different, then a #VALUE! error is thrown? Try to change to legitimate October 28...
    – Riccardo
    Jan 12, 2020 at 17:42
  • 1
    Ok the issue happens when a student has no markings for a given date. I'll fix this
    – Riccardo
    Jan 13, 2020 at 8:23
  • Thank you for your kind words. Glad you worked it out. You see? Happiness is contagious :-) Just fill the rest of the cells with 0s in a non-intrusive way
    – marikamitsos
    Jan 13, 2020 at 9:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.