Need formula to calculate a horizontal "running average" values in the last five columns

Hello,

I have attached two pictures to help paint the picture of my current challenge. We have created a tracker where every question a student has answered throughout the year in a particular subject is sorted by Standard Performance Indicator (SPI) and chronologically by date. (picture one). This allows our teachers to see how "Johnny" or "Jane" have done with a particular concept throughout the year.

The second picture shows the "roll-up" of these results by SPI on a "master page" with every student. This allows our teachers to see trends, i.e. "My first period doesn't understand addition, while my second period doesn't understand subtraction."

The formula is a simple AverageIf formula, and my teachers want to push the formula to the next level. They want the formula to only include the last five questions a student has been tested on in the calculation of the average. This makes sense. If a student didn't get "addition" in September, but gets it all of the time now, a teacher shouldn't waste time trying to teach them addition when they already get it.

I'm looking for an Average(IF or AverageIF formula that allows me to use Offset, Index to pull the last five values. Does anyone have any ideas on how to do this?

1.jpg2.jpg

Your post does not comply with Forum Rules.

Post a WORKBOOK. Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.