1

This seems pretty simple but I can't find how to do it.

I keep a Google Sheet where I add several times a day new lines as following :

  | A     | B   | C   |
  | ---   | --- | --- |
1 | John  | 10  | 50  |
2 | Peter | 20  | 70  | 
3 | John  | 14  |     |
4 | Peter | 12  | 82  |
5 | John  | 5   | 55  |

I'm trying to get the average rating of the last N lines for a given name. That way I can send a report to "name" every now and then with an average of his N last ratings.

Improve this question
1

1 Answer 1

Reset to default
1

Explanation:

Use a query to get the last N entries of a particular name and then get the average based on column C.

The trick here is to reverse the matrix: sort(A1:C5,ROW(A1:A5),False) so you get the last N rows.

Solution:

=INDEX(QUERY(
     QUERY(sort(A1:C5,ROW(A1:A5),False),"select * where Col1 contains 'John' limit 2"),
"select avg(Col2),avg(Col3)"),2,0)

this will calculate both the averages of the last N (2) entries of a particular name:

Example: Peter last 2 entries:

enter image description here

Example: John last 2 entries:

enter image description here

Improve this answer
12
  • Thanks for your answer, but it's not exactly what I need. this will calculate the average for John for N larger than 3, but I need the average of exactly John last N grades. Different names don't have same number of grades so if I can't specify 'last 30 grades' it will return me more grade for 1 pupil than for another, and I need exactly the average of the last 30.
    – Francis Kabalou
    Jan 8, 2021 at 15:36
  • @FrancisKabalou check again the answer.
    – Marios
    Jan 8, 2021 at 15:46
  • Instead of 3 and John you can use input from other cells by using their reference.
    – Marios
    Jan 8, 2021 at 15:55
  • 1
    wow, works perfectly ! Thanks a lot for taking the time to help me.
    – Francis Kabalou
    Jan 8, 2021 at 16:57
  • 1
    That is great @Marios One could also consider taking the parameters out of the formula.
    – marikamitsos
    Jan 8, 2021 at 17:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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