• 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)

Days of Grace

Days of Grace

International Captain
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.
 

Attachments

Last edited:
Athlai

Athlai

Not Terrible
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.
 

Attachments

Athlai

Athlai

Not Terrible
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.
 
Days of Grace

Days of Grace

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

Athlai

Not Terrible
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

ankitj

Hall of Fame Member
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

honestbharani

Whatever it takes!!!
Days of Grace said:
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.
Click to expand...

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

srbhkshk

International Captain
ankitj said:
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.
Click to expand...
I added a one month grace period so that we won't have border cases in the calculation.
 
honestbharani

honestbharani

Whatever it takes!!!
srbhkshk said:
This calculates all peaks (5 to 10) and puts an NA if the time interval wasn't sufficient.
Click to expand...
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.
 
You must log in or register to reply here.

Top