Hey all,
I've got a (hopefully) fun one for you. I've attached some dummy data: Running.xlsx
That spreadsheet tracks the distance an individual ran on a given day from 1/1/2014 - 2/28/2014 using two columns: Date (in descending order from 2/28/14 - 1/1/14) and Distance (the number of miles they ran that day).
What I am trying to see is, for each time the runner hits a new record distance, how well does the runner perform in the three days following the record? Does he hit a new record quickly after setting one? Does he slump and not hit another record for a few weeks??
For example, this is the data for the first 10 days in the attached spreadsheet:
Date Distance 1/10/2014 2.839 1/9/2014 1.871 1/8/2014 1.382 1/7/2014 1.295 1/6/2014 2.839 1/5/2014 1.131 1/4/2014 0.352 1/3/2014 0.546 1/2/2014 2.740 1/1/2014 1.131
For just that isolated section, this is the type of information I would like to be able to extract:
Record Date Record Distance Day 1 Post RD Day 2 Post RD Day 3 Post RD 1/1/2014 1.131 2.740 0.546 0.352 1/2/2014 2.740 0.546 0.352 1.131 1/6/2014 2.839 1.295 1.382 1.871
To be clear as to why the information in the bottom chart was selected, since there was no data prior to 1/1/14, the "Record Distance" is, by default, the distance ran on 1/1/14 - so that information is populated into the second chart. Along with it, we have the distances ran on 1/2, 1/3 and 1/4.
The next record distance was seen on 1/2/14, so that number is recorded on the next line followed by the distances seen on 1/3, 1/4 and 1/5. The following record distance isn't seen until 1/6, so the distance for 1/6 is recorded as well as 1/7, 1/8 and 1/9.
While that is all of the records seen in the first ten days of data, if you have the full Excel sheet opened, these are all of the record dates that should eventually be on the bottom chart:
1/15/2014 - 3.138 (have distance for 1/16, 1/17 and 1/18 also in row)
1/19/2014 - 3.210 (have distance for 1/20, 1/21 and 1/22 also in row)
2/15/2014 - 4.435 (have distance for 2/16, 2/17 and 2/18 also in row)
This is part of a larger analysis project I'm working on, so this data is a very small snippet of a much larger sample size. I want to try and nail down the methodology with a small sample size, however, before applying it to the larger set and seeing what (if any) trends are associated with the runner hitting a new distance record.
Anyone have any thoughts on how to get this done? Can it be done?
Bookmarks