Average separated array

[Jabe]

Hi,

Please see attached example. In the numbered columns I will put the marks for completed assignments. I would like to create a rolling average of the last 5 assignment scores. However, I need to keep the Intervention and Notes columns after each block of 5 assignments for a separate utility. Is there a way to average the last 5 values ignoring the Intervention and Notes columns?

 

[Scott R]

As long as row 2 has numbers for the assignments, this should work:
=SUMIF( B$2:Q$2, ">" & COUNT( B3:V3 ) - 5, B3:V3 ) / 5

or =AVERAGEIF( B$2:Q$2, ">" & COUNT( B3:V3 ) - 5, B3:V3 )