Five year peaks - point to point (excel help)

srbhkshk

International Regular
The issue with that data range is that we have no entries for 68 and 69 which means the 5 year period may not account for his 63, 64 performances in the formula.
I use dates as end points, if there were no tests in a given year then it can't be the end point anyway.

honestbharani

Whatever it takes!!!
I use dates as end points, if there were no tests in a given year then it can't be the end point anyway.
Yeah, I was more referring to the gaps in how the data can be. Yours and Ankits formulae were perfect.

Days of Grace

International Captain
See the formula in column M
Thank you for your efforts everyone!

This is very close to what I was looking for. However, the data should include the test after the five year period. Pollock played from 1963 until 1967 and then didn't play from 1968 until 1970. Therefore, his data from his first test match should include the first match he played in 1970 because that is when he crossed the five year threshold, so to speak.

ankitj

International Coach
Thank you for your efforts everyone!

This is very close to what I was looking for. However, the data should include the test after the five year period. Pollock played from 1963 until 1967 and then didn't play from 1968 until 1970. Therefore, his data from his first test match should include the first match he played in 1970 because that is when he crossed the five year threshold, so to speak.

You mean after each 5 year period should include one test just outside the 5 year period?

Days of Grace

International Captain
You mean after each 5 year period should include one test just outside the 5 year period?
Yes, exactly!

Days of Grace

International Captain
I mean exactly five years from the start of the first match to the start of the last match would be fine, e.g. 22/1/1965 - 22/1/1970.

So let's say "at least five years" between test matches, including the first and last match.

weldone

Hall of Fame Member
Hi DoG, Please find attached updated sheet with formula in colun N. It returns #REF! error when 5 year is not applicable. Hope this is what you wanted?

Attachments

• 24.1 KB Views: 9

Days of Grace

International Captain
Thank you, Weldone! That returns exactly the same averages as I manually calculated.

I have no idea what the numbers in M mean though or how you set up the formula in N.

I could copy the formulas to other players' spreadsheets but I really want to know how it actually works so I can learn something :-)

EDIT, I see. The data in Column M is how many matches are included in the five years after the first test.

weldone

Hall of Fame Member
Thank you, Weldone! That returns exactly the same averages as I manually calculated.

I have no idea what the numbers in M mean though or how you set up the formula in N.

I could copy the formulas to other players' spreadsheets but I really want to know how it actually works so I can learn something :-)

EDIT, I see. The data in Column M is how many matches are included in the five years after the first test.
Yes column L is integer progression 1, 2, 3...

Column M is number of matches in 5 years. You can simply copy this formula for other players.

Column N is the final formula. It basically tells the numerator and denominator to calculate sum of runs and sum of dismissals respectively starting from current match (i.e. column L integer progression) to the last needed match (which in your case is column L + column M)

=SUM(INDEX(\$A\$2:\$A\$24,L2,0):INDEX(\$A\$2:\$A\$24,L2+M2,0))/SUM(INDEX(\$B\$2:\$B\$24,L2,0):INDEX(\$B\$2:\$B\$24,L2+M2,0))

You need to change one thing in this formula for other players: see the number 24 appearing in the formula e.g. \$A\$24, \$B\$24 etc.? That is because the ranges for Pollock end in cells A24 and B24. For some other player who played different number of matches, just change the number 24 to wherever the ranges end.

Last edited:

Days of Grace

International Captain
Yes column L is integer progression 1, 2, 3...

Column M is number of matches in 5 years. You can simply copy this formula for other players.

Column N is the final formula. It basically tells the numerator and denominator to calculate sum of runs and sum of dismissals respectively starting from current match (i.e. column L integer progression) to the last needed match (which in your case is column L + column M)

=SUM(INDEX(\$A\$2:\$A\$24,L2,0):INDEX(\$A\$2:\$A\$24,L2+M2,0))/SUM(INDEX(\$B\$2:\$B\$24,L2,0):INDEX(\$B\$2:\$B\$24,L2+M2,0))

You need to change one thing in this formula for other players: see the number 24 appearing in the formula e.g. \$A\$24, \$B\$24 etc.? That is because the ranges for Pollock end in cells A24 and B24. For some other player who played different number of matches, just change the number 24 to wherever the ranges end.

Thank you again, you have saved me months of work! I’ll publish my findings of five year peaks a.s.a.p.

weldone

Hall of Fame Member
Would love to see the results, also for bowlers

Days of Grace

International Captain
Unfortunately, the five year peak rating doesn't work for Pat Cummins, who play one test in 2011 and then didn't play again until 2017. So they only time we has played two test matches during a period of five years was from 2011 to 2017.

It may be better to just stick to calendar years, but then again you have players who didn't string together five consecutive calandar years at any point in their careers.

I wanted a change from 40 innings or 50 innings because a player from this century may complete 40 innings in 2 years but a player from 70 years ago may have taken 8 years to do the same thing.

honestbharani

Whatever it takes!!!
I would suggest assigning serial numbers to each inning performance and take a rolling list of 50 or 40 as it might be the best indicator. You can era adjust the number as well, considering how many more games the more modern players play etc.

ankitj

International Coach
I would suggest assigning serial numbers to each inning performance and take a rolling list of 50 or 40 as it might be the best indicator.
I think that's what he did previously.

honestbharani

Whatever it takes!!!
Yeah but you can adjust the number based on eras, which was the second part of my suggestion.

Days of Grace

International Captain
Yeah, I'm strongly considering adjusting by era.

Days of Grace

International Captain
Test Batsmen Top 10 Five Calendar Year Peaks (Ranked by adjusted average, adjusted runs per innings and adjusted strike-rate; 40 innings required to get full points)

 Runs Adj. Ave. (Orig.) Adj. RPI (Orig.) Adj. S/R (Orig.) Innings 1. DG Bradman 1930-1934 3381 98.14 (105.66) 92.36 (99.44) 73.57 (70.60) 34 2. JB Hobbs 1910-1914 2031 76.46 (65.52) 67.72 (58.03) 60.37 (62.09) 35 3. IVA Richards 1976-1980 3158 69.13 (70.18) 67.63 (68.65) 65.55 (66.37) 46 4. GS Sobers 1958-1962 3104 72.26 (77.60) 62.84 (67.48) 61.66 (53.56) 46 5. SPD Smith 2014-2018 5229 68.13 (71.63) 57.83 (60.80) 51.96 (57.05) 86 6. RT Ponting 2002-2006 6041 67.64 (72.78) 57.88 (62.28) 54.46 (61.68) 97 7. CL Walcott 1954-1958 2157 66.55 (69.58) 58.94 (61.63) 65.62 (56.03) 35 8. GA Faulkner 1907-1911 1394 69.60 (58.08) 64.24 (53.62) 56.65 (57.96) 26 9. C Hill 1898-1902 1744 65.45 (54.50) 61.60 (51.29) 57.54 (57.63) 34 10. KC Sangakkara 2006-2010 4422 64.94 (71.32) 58.35 (64.09) 51.77 (58.33) 69

Last edited:

Days of Grace

International Captain
Test Bowlers Top 10 Five Calendar Year Peaks (Ranked by adjusted average, wickets per innings and adjusted strike-rate; 40 innings required to get full points)

 Wickets Adj. Ave. (Orig.) WPI Adj. S/R (Orig.) Innings 1. Imran Khan 1982-1986 138 14.39 (14.31) 3.45 39.62 (39.13) 40 2. M Muralitharan 2002-2006 287 17.36 (18.08) 3.93 48.56 (45.54) 73 3. SF Barnes 1910-1914 122 17.72 (14.08) 4.52 43.89 (35.07) 27 4. Sir RJ Hadlee 1984-1988 191 17.97 (18.15) 3.47 43.01 (43.90) 55 5. Waqar Younis 1990-1994 184 18.92 (18.49) 3.35 36.64 (35.38) 55 6. JC Laker 1955-1959 114 16.18 (15.69) 2.53 49.84 (57.14) 45 7. GA Lohmann 1892-1896 54 13.87 (10.07) 3.86 43.28 (33.61) 14 8. Shoaib Akhtar 2000-2004 110 18.80 (19.32) 2.82 38.50 (35.15) 39 9. MD Marshall 1985-1989 165 18.66 (18.35) 2.84 43.51 (42.58) 58 10. DW Steyn 2007-2011 228 19.87 (21.08) 2.96 39.84 (37.72) 77

Last edited: