Five year peaks - point to point (excel help)

[Days of Grace]

Hope everyone is doing well during these weird times.

I'm working on my next version of my ratings for test batsmen and test bowlers. Also working on the ODI version.

I've decided to measure a batsman or bowler's peak by 5 years rather than a number of innings. I noticed that, particularly for modern players, if you happen to be in great form, your 40 innings peak may only last for 2 years, which is a lot less meritorious than being at the top of your game for 5 years.

The difficulty that I am having is how to get excel to measure five year increments if I give it the data of a batsman's career. I'm attaching Graeme Pollock's example. He averaged a remarkable 81.79 between 12 February 1965 and 19 February 1970 (there must be at least 5 years between the first and last test matches).

How can I get microsoft excel to calculate this for me? Rather than me doing everything manually, which would be very time consuming.

I would appreciate any help from any excel gurus. Cheers.

Meantime, please share with me the best five year point-to-point peaks for batsmen and bowlers that you can find in statsguru.

 

[vcs]

I hear @Daemon is good with spreadsheets.

 

[weeman27bob]

You can attach Zip files so you could zip up the spreadsheet and then attach that to a post

 

[James]

EDIT: I see that I can't attach excel extensions here anymore. If you can help me, please message me and I can email the excel file to you. Cheers.

You can now attach spreadsheets

 

[Days of Grace]

Thanks, James!

 

[Athlai]

Yeah this is certainly possible though it's a bit beyond me cracking it off the top of my head though. A few more experienced excellers should be able to get you the right result.

I've thrown it into excel and got this result.

No idea if I've done it correctly as my formula is very bits and pieces.

 

[Athlai]

Actually I've got no idea why I tried to do the range thing for year, simply adding +5 to date value its checking in years would work. OOf.

 

[Athlai]

My date function is all wrong

 

[Days of Grace]

That's a good effort, but you can tell that it's calculating from the current point to the end of the career.

 

[Athlai]

Yeah my running average formula is entirely disjointed from the date range. Date range is probably going to end up something like IF(DATE(YEAR(VALUE),MONTH(VALUE)DAY(VALUE)>=(DATE(YEAR(VALUE)+5,MONTH(VALUE),DAY(VALUE)
then give us...

XYZ

I will give up though and let the more experienced hands allow me to stop embarrassing myself

 

[ankitj]

See the formula in column M

 

[Athlai]

Look at that handsome devil with his sleek formula

 

[srbhkshk]

This calculates all peaks (5 to 10) and puts an NA if the time interval wasn't sufficient.

 

[Athlai]

Look at this guy doing it backwards like a cool kid.

 

[ankitj]

Tricky thing is that you get different set of results depending on whether you setting 5 year window starting from date of given test or ending at date of a given test.

 

[honestbharani]

Hope everyone is doing well during these weird times.

I'm working on my next version of my ratings for test batsmen and test bowlers. Also working on the ODI version.

I've decided to measure a batsman or bowler's peak by 5 years rather than a number of innings. I noticed that, particularly for modern players, if you happen to be in great form, your 40 innings peak may only last for 2 years, which is a lot less meritorious than being at the top of your game for 5 years.

The difficulty that I am having is how to get excel to measure five year increments if I give it the data of a batsman's career. I'm attaching Graeme Pollock's example. He averaged a remarkable 81.79 between 12 February 1965 and 19 February 1970 (there must be at least 5 years between the first and last test matches).

How can I get microsoft excel to calculate this for me? Rather than me doing everything manually, which would be very time consuming.

I would appreciate any help from any excel gurus. Cheers.

Meantime, please share with me the best five year point-to-point peaks for batsmen and bowlers that you can find in statsguru.

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

 

[ankitj]

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

Please comment on this @Athlai

 

[srbhkshk]

Tricky thing is that you get different set of results depending on whether you setting 5 year window starting from date of given test or ending at date of a given test.

I added a one month grace period so that we won't have border cases in the calculation.

 

[honestbharani]

This calculates all peaks (5 to 10) and puts an NA if the time interval wasn't sufficient.

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.

 

[honestbharani]

Please comment on this @Athlai

Hey, I am not an excel guru. I just use the simplest means I can.