0

Doing some sports statistical analysis for fun. Wonder if any of you guys can help me.

This is the sheet I'm working with. I'm sure you can see how I'm already experimenting with this in the Z-Score rushing attempts column. I already know how to find Z-Score with formula in Sheets using AVERAGE and STDEV. What I have done in the past is just find the Z-Score for every cell in a fixed five-year range. So, for instance, I will calculate the STDEV and AVERAGE for all of the teams from 1941-1945, and all of the Z-Score for each individual team statistic (each individual cell) will come from that range, whether that team is from 1941 or 1945.

What I want if for there to be a rolling calculation that places the year that the team is being calculated at the center. So all of the teams from 1941 would instead be compared to teams from 1939-1943. All of the teams from 1942 would be compared to teams from 1940-1944, etc.

I have tried to just use a formula (let's say it looks something like =((d66-AVERAGE(d2:d162))/STDEV(d2:d162) that compares, for instance, the 2019 Washington Redskins' rushing attempts in that sheet to teams in the range from the second row to the 161st row, which constitutes all of the teams from 2021-2017. But as I'm sure you can tell, that doesn't work for every cell. Because if I then autofill this formula to compare the next row down, the 2019 Tennessee Titans, they aren't being compared to all of the teams from 2021-2017. They are then being compared to all of the cells from rows 3-162, which does not include the 2021 Washington Football Team and instead includes the 2016 Washington Redskins.

I guess this is what this really comes down to. Is there any way that I can write a formula that uses the fact that in Column A I have labeled each row with the year (or label the rows by year using Named Range) so that I can accomplish this goal of calculating Z-Score by rolling five year data sets?

Improve this question

1 Answer 1

Reset to default
0

The first thing to do is to make a column containing the relevant year for each team, which is stored in the first four characters of your "Team" column. You could use =VALUE(LEFT(A2, 4)) for that.

Then you can make another column for the z-score that uses array formulas to calculate the averages and standard deviations only for the years in your five year ranges. I made the below formula in a dummy sheet, and you could edit it to fit your sheet. Make sure you use absolute references where I have, and then do a manual check for an arbitrary team and year to make sure that the formula is giving the same result as you selecting the ranges for the average and standard deviation manually.

=ArrayFormula((C1-AVERAGE(IF(ISBETWEEN($A$1:$A$1000,A1-2,A1+2),$C$1:$C$1000)))/STDEV(IF(ISBETWEEN($A$1:$A$1000,A1-2,A1+2),$C$1:$C$1000)))

C1 = Rushing attempts for one team in one year

$A$1:$A$1000 = Absolute reference for all years (the column you made using the LEFT() function)

A1-2 = Start of five year range for this row's year

A1+2 = End of five year range for this row's year

$C$1:$C$1000 = Absolute reference for all rushing attempts

Improve this answer
8
  • Robert, thank you so much! This is everything in an answer I could have ever dreamed of. And already written up for me! I do have one thing though. I'm getting a division error, saying that the AVERAGE function is causing a division by zero. I figured this might be because I started in 2021, where there's no reference for a1+2, but this was not changed when I went to 2019. Any idea why this might be? Currently going over some things trying to fix it myself, I'll let you know if I have any luck.
    – Levi
    May 11 at 6:07
  • Can you show here the edited formula you've made? The AVERAGE function shouldn't be causing a division by zero error if it's set up properly.
    – Robert Graham
    May 11 at 6:24
  • =ArrayFormula((E66-AVERAGE(IF(ISBETWEEN($B$2:$B$2101,B66-2,B66+2),$E$2:$E$2101)))/STDEV(IF(ISBETWEEN($B$2:$B$2101,B66-2,B66+2),$E$2:$E$2101))). The spreadsheet has 2101 rows of information, I was starting at row 66 because that's the first 2019 row that would include a five year set. I've also checked columns B and E. there shouldn't be an zeros in those columns.
    – Levi
    May 11 at 6:29
  • "E" is the column for rushing attempts and "B" is the column for year.
    – Levi
    May 11 at 6:36
  • I've worked out the problem I think. Your column of years (B) is set up as text, rather than numbers, so it's breaking the formulas. If you select the range of years and go Format > Number > Automatic, that should convert them from text to numbers, and the formulas will work. Alternatively, you could edit the formula for your Year column to =VALUE(LEFT(A2,4)). I'll edit the answer to include that step.
    – Robert Graham
    May 11 at 6:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.