Moving Average based on specific values

[monziskas]

I understand how to use OFFSET to calcualte a moving average to say average the 4 values. However, I need to skip rows and only include the rows that match specific criteria. As example, If column A contains employee name and column B contains number of sales. How do I calculate the Bob's Average sales for the last 4 weeks.

Bob 3
Mary 2
Susan 1
Bob 2
Mary 6
Susan 1
Bob 5
Mary 3
Susan 7
Bob 3
Mary 1
Susan 9
Bob 1
Mary 5
Susan 3

using offset like the below I can get an average of the last for weeks. but how do I get the average of the last for weeks where column A = Bob
=AVERAGE(OFFSET(B15,0,0,4,1))

[jkray622]

Have you tried the "Averageif" or "Averageifs" functions? Seems like =Averageif(A1:A15,"bob",B1:B15) will average just the values that have Bob.

As far as setting them to particular weeks, you have no date ranges in your data, so I'm not sure what you want with that? Are you trying to only calculate the average of the last 4 of Bob's entries? Will they always be sequentially? What if Bob is out sick for a week--would you want to calculate zero for him, or leave that week off the calculation?