# AVERAGEIF on last N rows

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.

• Please check the edited question and confirm it is displayed correctly. Jan 8, 2021 at 15:54

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:

Example: `John` last `2` entries:

• 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. Jan 8, 2021 at 15:36
• @FrancisKabalou check again the answer. Jan 8, 2021 at 15:46
• Instead of `3` and `John` you can use input from other cells by using their reference. Jan 8, 2021 at 15:55
• wow, works perfectly ! Thanks a lot for taking the time to help me. Jan 8, 2021 at 16:57
• That is great @Marios One could also consider taking the parameters out of the formula. Jan 8, 2021 at 17:37