Average of Last x Instances of a Criteria

[whothemannow]

Hi all,

I am working on a spreadsheet about employee performance, with columns of employee name, month, year, performance, etc. (as a result, employees have multiple rows as the data is sorted by dates). How can I calculate the average of each employee's last 6 performance scores (excluding zeroes)? (so that each row would return that employee's average) I have tried numerous formulas and initially thought that I should use AVERAGEIF, but I can't seem to figure it out.
Any help would be greatly appreciated!

 

[whothemannow]

Change the 2 after the sequence to 1

It works now, but it doesn't seem to be super consistent, the formula seems to only take into account the first 6 responses, is there a way for it to take into account the last 6? Ideally, it would also be good if zeroes were excluded from the average calculation. Column M in the screenshot below is the value that the formula should return, as opposed to what it does (column L).

 

[Fluff]

Can you post your sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.