Rolling Average in Athlete Database

[PerryN1]

Hi all, I'm running Powerpivot in excel 2013 and have several tables of data whereby a squad of players complete a several tasks once per week, e.g.

Date Player Score1 Score2 Score3

08/02/14 Player1 10 20 20
08/02/14 Player2 13 16 21
08/02/14 Player3 7 26 31
01/02/14 Player1 15 21 23
01/02/14 Player2 25 16 21
01/02/14 Player3 65 21 14

I'm trying to calculate a rolling Z-score (Latest Score - Rolling 8-week average score/Rolling 8 week standard deviation) for each player for each score in a new column (so a z-score column for each score). As a newbie to Powerpivot, I have no idea how to go about this so any advice would be greatly appreciated.

 

[Tianbas]

maybe not the full solution but something to start

first add a weekID column so you have the weeks as numbers from 1 to 52 (or more if you have more years)

the moving 8 week average by player you could get by
=calculate(average(Table1[Score1]),filter(Table1,Table1[Player]=EARLIER(Table1[Player]) && Table1[Week_ID]<=EARLIER(Table1[Week_ID]) && Table1[Week_ID]>EARLIER(Table1[Week_ID])-8))

 

[PerryN1]

Thanks Tianbas,

Unfortunately I got the following error:

Too few arguments were passed to the FILTER function. The minimum argument count for the function is 2.

Any ideas?

 

[Tianbas]

Formula should work (at least it does on my PC). The error you get if something is missing in the FILTER formula, so please check again if you have all , && () in the right place

 

[PerryN1]

Yeah it does work actually, many thanks for your help!

 

[PerryN1]

Another question Tianbas, I now have the following table:

Week|Date|Player|Score1|Score2|Score3|Score1_Last8WeekAvg|Score2_Last8WeekAvg|Score3_Last8WeekAvg

In a separate table I have fixed (i.e. not rolling) standard deviations of each score for each athlete:

Player|Score1_stdev|Score2_stdev|Score3_stdev

The first part of the z-score formula will be =(Score1- Score1_Last8WeekAvg)/

And then I'm looking to divide that by the standard deviation for the given athlete from the second table i.e. =(Score1- Score1_Last8WeekAvg)/Score1_stdev

Any suggestions?

 

[Tianbas]

so what you need is a kind of VLOOKUP to the 2nd table. In PowerPivot this can be done by the RELATED() function. Both of your tables should have a relationship by player.

Your formula would look like following
=(Score1- Score1_Last8WeekAvg)/RELATED(table2[Score1_stdev])