Five year peaks - point to point (excel help)

[Athlai]

You can simply introduce a filter to the column "Date" and then filter out 5 year periods.

Get ****ed

 

[srbhkshk]

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]

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]

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]

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]

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

Yes, exactly!

 

[Days of Grace]

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]

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?

 

[Days of Grace]

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]

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.

 

[Days of Grace]

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]

Would love to see the results, also for bowlers

 

[Days of Grace]

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]

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]

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]

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

 

[Days of Grace]

Yeah, I'm strongly considering adjusting by era.

 

[Days of Grace]

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

 

[Days of Grace]

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

 

[Adders]

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.