Moving AVERAGEIF with OFFSET

[Kev4022]

Good Afternoon,

My data is constantly updated (new rows) and i'd like to be able to average the last 3 scores (SCORE) for each Student (STUDENT) - in the example below Student A would show an average of 91, 71, 74. The issue arises when I add new data, I need this to update on the fly. This is an inherited system and cannot change the layout in anyway.

Edit: This is the formula I've been trying to play with:
=AVERAGEIF(A:A, "A", OFFSET(B2,COUNT(B:B),0,-3,1))

Edit 2: I am keen on learning so if you have time to explain any answers that would be great! As with the above formula I'm trying to follow it logically with the Range, Criteria and Average Range but clearly I'm failing somewhere!

Any help would be much appreciated!


[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD="align: center"]STUDENT[/TD]
[TD="align: center"]SCORE[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]100[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]91[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]55[/TD]
[/TR]
[TR]
[TD="align: center"]D[/TD]
[TD="align: center"]62[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]71[/TD]
[/TR]
[TR]
[TD="align: center"]C[/TD]
[TD="align: center"]68[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]74[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

This works i believe but requires CTRL-SHIFT-ENTER:

=AVERAGE(IF((A2:A100="A")*(ROW(2:100)=LARGE((A2:A100="A")*ROW(2:100),{1,2,3})),B2:B100))

Its a little bit complicated so the explanation I haven't really got the time for. It essentially produces 3 arrays to test against the first array and produces an array of FALSE and 3 numerical answers. AVERAGE will ignore the FALSE parts so produce an average of the 3 values (or less if you only had 2 'A' s for example.
​

 

[Kev4022]

This works i believe but requires CTRL-SHIFT-ENTER:

=AVERAGE(IF((A2:A100="A")*(ROW(2:100)=LARGE((A2:A100="A")*ROW(2:100),{1,2,3})),B2:B100))

Its a little bit complicated so the explanation I haven't really got the time for. It essentially produces 3 arrays to test against the first array and produces an array of FALSE and 3 numerical answers. AVERAGE will ignore the FALSE parts so produce an average of the 3 values (or less if you only had 2 'A' s for example.
​

Genius! Thank you so much! My laptop is no longer in danger of annihilation!

 

[steve the fish]

Just make sure the size of the range is always greater than your data in terms of number of rows to test or you are going to get incorrect result, ie change the 100 in the formula to suit the amount of data you are ever likely to get. Using full columns would work but that's a slow formula then.