• Welcome to the Cricket Web forums, one of the biggest forums in the world dedicated to cricket.

    You are currently viewing our boards as a guest which gives you limited access to view most discussions and access our other features. By joining our free community you will have access to post topics, respond to polls, upload content and access many other special features. Registration is fast, simple and absolutely free so please, join the Cricket Web community today!

    If you have any problems with the registration process or your account login, please contact us.

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.
 

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.

Ankij, can you adjust your formula to fit what I'm saying?
 

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.

Ankij, can you adjust your formula to fit what I'm saying?
You mean after each 5 year period should include one test just outside the 5 year period?
 

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.
 

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.
 

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.
 

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)


RunsAdj. Ave. (Orig.)Adj. RPI (Orig.)Adj. S/R (Orig.)Innings
1.DG Bradman1930-1934338198.14 (105.66)92.36 (99.44)73.57 (70.60)34
2.JB Hobbs1910-1914203176.46 (65.52)67.72 (58.03)60.37 (62.09)35
3.IVA Richards1976-1980315869.13 (70.18)67.63 (68.65)65.55 (66.37)46
4.GS Sobers1958-1962310472.26 (77.60)62.84 (67.48)61.66 (53.56)46
5.SPD Smith2014-2018522968.13 (71.63)57.83 (60.80)51.96 (57.05)86
6.RT Ponting2002-2006604167.64 (72.78)57.88 (62.28)54.46 (61.68)97
7.CL Walcott1954-1958215766.55 (69.58)58.94 (61.63)65.62 (56.03)35
8.GA Faulkner1907-1911139469.60 (58.08)64.24 (53.62)56.65 (57.96)26
9.C Hill1898-1902174465.45 (54.50)61.60 (51.29)57.54 (57.63)34
10.KC Sangakkara2006-2010442264.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)

WicketsAdj. Ave. (Orig.)WPIAdj. S/R (Orig.)Innings
1.Imran Khan1982-198613814.39 (14.31)3.4539.62 (39.13)40
2.M Muralitharan2002-200628717.36 (18.08)3.9348.56 (45.54)73
3.SF Barnes1910-191412217.72 (14.08)4.5243.89 (35.07)27
4.Sir RJ Hadlee1984-198819117.97 (18.15)3.4743.01 (43.90)55
5.Waqar Younis1990-199418418.92 (18.49)3.3536.64 (35.38)55
6.JC Laker1955-195911416.18 (15.69)2.5349.84 (57.14)45
7.GA Lohmann1892-18965413.87 (10.07)3.8643.28 (33.61)14
8.Shoaib Akhtar2000-200411018.80 (19.32)2.8238.50 (35.15)39
9.MD Marshall1985-198916518.66 (18.35)2.8443.51 (42.58)58
10.DW Steyn2007-201122819.87 (21.08)2.9639.84 (37.72)77
 
Last edited:

Adders

Cricketer Of The Year
I will give up though and let the more experienced hands allow me to stop embarrassing myself
Can you keep in mind mate that when you embarrass yourself, you also embarrass me. Just saying.

But ftr, you may as well be speaking another language to me.........when I think of excel, I think of Jimmy Anderson.
 

Top