Moving Average

[tally]

I'm having problems figuring out the proper combination of functions to
use. Each week a person shoots a score. They can have anywhere from
1-12 scores over a 12 week period. I'm trying to generate a "moving"
average each week for each person using their 3 most recent scores.

This is the current format, but I'm looking to automatically generate
column D (the Average).

A1 B1 C1 D1
Date Person Score Average
115 Mike 40 40
115 Ike 10 10
122 Mike 20 30
122 Ike 30 20
127 Mike 30 30
127 Ike 80 40

I was able to figure out how to average all of a person's scores
through the current week using the sumif function.
=sumif($B$2:B10,B10,$C$2:C10) / countif($B$2:B10,B10)

I can't seem to figure out how to:

Find the person (Column B)
Locate their 3 most recent scores (I'm assuming I would do so using the
Date value as the search criteria).
Average the values from the Score column.

Any suggestions would be appreciated.

 

[Frank Kabel]

Hi

one question upfront regarding your date column: what are you entering
or did you format it with a custom format. Assuming that the date
column consists of Excel dates or at least ascending numbers for each
person you can use the following formulas:

1. Evaluate the starting date for your moving average per person:
=LARGE((B1:B999=person_name)*(A1:A999),3)
entered as array formula (CTRL+SHIFT+ENTER)

2. Get the sum for the last 3 entries per person:
=SUMPRODUCT((B1:B999=person_name)*(A1:A999>=LARGE((B1:B999=person_name)
*(A1:A999),3)),C1:C9)
(don't has to be entered as array formula)
Now just divide this SUM with 3 and you get your moving average for
person_name

For the case that there a less than 3 entries for one person you cannot
divide the sum by 3 but you have to calculate the number of entries and
devide by this result:
3. Get the relevant number of entries per person
=SUMPRODUCT((B1:B999=person_name)*(A1:A999>=LARGE((B1:B999=person_name)
*(A1:A999),3)))

So to calculate your moving average use the follwoing formula
=SUMPRODUCT((B1:B999=person_name)*(A1:A999>=LARGE((B1:B999=person_name)
*(A1:A999),3)),C1:C9)/=SUMPRODUCT((B1:B999=person_name)*(A1:A999>=LARGE
((B1:B999=person_name)*(A1:A999),3)))

HTH
Frank

 

[tally]

Thanks very much for the detailed explanation. I don't think I would
have figured this formula out on my own.

The date is currently entered as a number, but there's no reason I
couldn't enter the date format as long as I can calculate based on
that.

Thanks again!

 

[Frank Kabel]

Hi
thanks for the thanks.
Regarding your date entry. I assume you enter them in the format MMDD.
If yes, no need to chang anything as long you're only evaluating one
year.

Frank