As an overview, there are about 20 people in a running "club" that I've been in since July. We've all been posting times of our sprint interval in a Google Spreadsheet. But no one can easily see how they've been improving over time. So I want to graphically represent that through http://d3js.org/. In order to do that, I first need to get the data structured correctly!
So the main pieces of data area:
- Name
- Date
- Activity (Run, Bike, Row)
- Distances Covered
- Times
Each "set" of runners data is separated by 11 rows. There are FOUR rows that separate each runner -- where they enter their date, activity run, header for time / distance.
My first 200m entry is on C8.
i have 8 intervals listed on C9, C10, C11, C12, C13, C14, C15, and C16.
The next person's (Crystal) first 200m entry is C19
and she has her intervals listed on C20, C21, C22, C23, C24, C25, C26
The third person's (Mindy) first 200m entry is C30.
and she has her intervals listed on C31, C32, C33, C34, C35, C36, C37, C38.
See the pattern? (I could not just copy/paste the formula because each runner has their own "header" section - 4 rows- with Date / Distance / Time and the formula entered that.)
This is the "logic" that I used to set up the formulas.
- If there is no time entered in Columns C, E, G, I then the runner did not run that day, and no times will be logged. Hence IF(NOT(ISBLANK
- If there is a time entered in Columns C, E, G, then there is a distance associated with it in Columns (B, F, and D)
- If there is a distance logged, then there is an activity (run, bike, row) to associate.
- If there are times, distance, to log, then it should be associated with a runner's name.
Here are the Formulas:
- Name: =IF(NOT(ISBLANK('Source Data'!C8)),'Source Data'!A8) <--- Where SourceData C8 is the TIME.
- Date: =IF(NOT(ISBLANK('Source Data'!C8)),'Source Data'!$C$5) <--- Where $C$5 is where the date is.
- Activity: =IF(NOT(ISBLANK('Source Data'!C8)),'Source Data'!$C$6) <--- Where $C$6 is the activity
- Distance: =IF(NOT(ISBLANK('Source Data'!C8)),'Source Data'!B8) <---- Where B8 is the distance
- Time: =IF(NOT(ISBLANK('Source Data'!C8)),'Source Data'!C8) <----- Where C8 is the time.
Then, there are columns of Running Data. Essentially 1 week includes 3 days of sprint running intervals.
So every column that I move over needs to be added by 2 column cells.
-- Day 1 200m's are in C8. Day 2's 800s are in E, Day 3's 5k is in G. So how can I have the formula for each "category" be updating by two columns?
So any help there? I've been at this for a while I feel like I'm so close!
If you know how to update the formula to add those "+ Rows, + columns" logic, that would be golden! Once I can get all this together, I'll then need to transpose it to have a sheet with only 5 columns: Name / Date / Activity / Distance / Time which will then be used for http://d3js.org/. I think I found out a few formula options for Transposing every n cells.
Bookmarks