How to mathematically have formula updated for n rows and y columns? (11 rows, 2 columns)

[mcnamc2]

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.

[MarvinP]

Hi mcnamc2 and welcome to the forum,

I'm afraid you have fallen into the same trap many others have found. You have created a wonderful looking spreadsheet that isn't very efficient. I call you type of work a mixed Cross Tab table. Excel doesn't work well with this type of format as you are finding.

My suggestion, which I'd do for you if it was easy, is to have a simple 4 column table: Name, Date, Distance, Time and then do pivot tables with it to show what you now are trying to accurately input. ...

I played with your data a bit and made up fake data on a new sheet. Then I calculated the Week Number and DOW using the date. Then did a Pivot Table that shows you an example of Pivots. You need to do your data in a TABLE that has no blank rows or columns. Then Pivot Tables can do all the work you are manually needing to do. See the attached with fake numbers.

[mcnamc2]

Hi! Thanks for letting me know that I was making it harder than it needs to be.

As you understood, I am simply trying to get all these data into the four columns: Name / Date / Distance / Time -- Are you saying a Pivot Table is what I need to do?

I could get these all into the 4 columns, but that would be a lot of copy / pasting! Do you have a suggestion of how I should handle this? This is over 5 months for 20 people, so it would take me some time! I want to be able to track the progress from July - November.

Thank you so much for your speedy response!

[MarvinP]

Hi,

I just converted your data, which needed lots of clean up to what I think you want. See the attached. You owe me.....

[mcnamc2]

Marvin! I am speechless!!!!! Thank you so much for doing this for me. I am going to work on a new form entry for the running group so that it conforms to this structure. You're really awesome. I have been at this for a month, and I sure wish I found this group earlier! THANKS A MILLION!!

[mcnamc2]

Wait, I looked at the data -- and I don't think it *quite* lines up. There may be something shifted?

For instance,
On 8/23/2012 Grace ran 800 m in 03:04, yet in the data (AB:101, it shows up that she ran it in 01:53.) I suspect that it is because the number format may not be set properly?

Another example shows Autumn running 1 mile on 9/4/2012 (B:43) -- She did not run that on 9/4 -- She ran 200 meters?

Looks like it's super close though!?

[MarvinP]

Hey,

I used the dates that were in Row 5 for the entire column of dates. I saw you had 7/23/12 for Catharine but change it to 7/24/12 for Mindy. I used 7/23 for all the data in Columns B and C.

I needed to convert all your "800" for distances to "800m" and all those others you didn't have a m or mile after.

In cell CW5 you didn't have a date at all so I put in 11/15/2012 and in CY5 I needed to fill in a real date also. Your data was messy!!!

Looking at the pivot table - it shows the AVERAGES of the times for each distance for that month. If you want to see the individual times you need to UNGROUP the rows so they show as single run times instead of Averages. Although - if a person ran two 800m in the same day it might still average those two times for that event.

You really need to play with pivot tables and see what format the data looks best in. A pivot chart might be a lot more appropriate and easier to use and display. BUT - I'm sure the structure of the data is MUCH easier to deal with in a table format instead of what you had.

Hope all this helps!!! I got two new knees last year so am jealous that you can still run.. Keep it up!!

[mcnamc2]

Hi Marvin!

I am sorry I did not get an e-mail notification that you responded!

I am not sure if I understand correctly, how does your insertion of dates account for the discrepancy in data?

Some data sets are blank because runners did not run at all for that day / interval.

At this point I'm not hung up on Pivot Tables. I'm more hung up on getting the data populated properly in the columns, including all the times logged for each distance run and its date?

Let me know if there's something I should be doing manually? But it looks like you got it set up pretty darn close and it may just need one more tweak in the macro?

How are your knees feeling? Did you get them replaced or?

[MarvinP]

Hi,

I took a macro I created last year to convert CrossTab tables into Excel Tables and tweaked it a bit to grab 2 columns instead of 1. I included that macro behind the sheet I gave you back. If you run the macro it will ask for how many columns are fixed and I put in 1. Then it takes chunks of data and mindlessly moves them down. The intent was to show you what a "good" table looks like in Excel. I didn't retype any of your numbers for times. I have no idea if your original rows of data were correct or not. Step though the macro and watch it work. Find the mistake and see if the macro or your original data was wrong.

[mcnamc2]

Hiya Marvin!

Believe me, I am trying to understand, but I have very limited experience with Macros.

You are saying, "If you run the macro" it will ask for how many columns are fixed, and I put in 1. I got that far, and did that.

"Then it takes chunks of data and mindlessley moves them down." -- Mindlessly does not assure me, I want the data to be moved down logically!

What do you mean, "I didn't reatype any of your numbers for time." Is that something that needs to be done? Re-typing in time?

and you say, "I have no idea if your original rows of data were correct or not. Step through the Macro and watch it work." I tried but was a bit confused. I couldn't really understand the code it was saying. Nor did I understand what to do when I found a mistake?

Sorry I am so dense. Would it be possible for you to provide more step-by-step instructions on what I need to do to make sure it populates properly?

Thanks!
Catharine

[MarvinP]

Hi Catharine,

You really aren't dense. You just structured your data to look nice, like you wanted it. That format of the data doesn't let Excel work with it well. My Macro was an attempt to save what you had already typed in. It was really a one-time conversion. I needed to pack your data together so it looked like a crosstab table by deleting lots of rows first. I ended up with what is on your first sheet. Then I copied it to the last sheet and ran my macro on it.

I was hoping you would see the structure of the table and continue putting in data in that format.

You said that some times were off. I would need to see which ones and try to decide if it was my macro that was at error or if they were in the wrong rows to begin with. I'm glad you checked some of the times. How many of the times do you believe were off?

If you respond to this and tell me if there is a newer version of what you have, I'll give it another try and run my macro on it again. Before I do that, make all your distances the same. 800 or 800m? Put a letter in all those distances so they aren't treated as numbers but as text. Also, why are some of the times on the left and others on the right? All that is just poor data entry. You clean up what was input and it is easier to run a macro on it.

[mcnamc2]

Ah, I see! So it is the last sheet where I should run the Macro
. I was trying the Step-Thru on the first sheet.

I will look at it in the morning and let you know!

Catharine

[MarvinP]

I've really run the macro on your data after making it look like my first sheet. I had to get rid of extra stuff so my macro would work. The idea is you should start using how the data looks like on the last sheet. I just gave you the macro as I was showing off. Running the macro again of the last sheet will just mess things up. The macro needs to be run on data that looks like my first sheet.

[mcnamc2]

Hi Marvin,

Thanks again for hanging in there with me. I just want to be able to take advantage of the power of your macro and get it right!

So on the sheet you gave me, "Pivot Table Running INtervals 2" :
"Source Data" features the data that you set up with all the "extra rows" deleted, as well as populating the first row with a date. Got it! Thanks for doing that!

I think the Macro is shifting down one row. Here's why I think that:

On Sheet 2, it says that Jeff Lutton ran 2.15 miles in 20:00 --
It was actually Autumn, the next row down that ran 2.15 miles. (Jeff did not run) -- So is the macro off by one row?

Erica ran 2.41 miles in 20:00 (Source Data, BZ, 63), but on Sheet 2 it is displaying that Krista ran it (Sheet 2, 123), the next row down.

So it looks like the Macro is off by only one row, so how could/ should I fix that? You mentioned I could do that in the step-through process, but I'm afraid I need insight on how I can step through.

Thank you!
Catharine

[MarvinP]

OK Catharine,

I've cleaned up my code and think my answer is correct now. Here is what to do. Take the first sheet and copy the whole thing to a new sheet. You do this by clicking on the cell above row 1 and to the right of Column A. It really isn't a cell but a box that will select the whole sheet. While the whole sheet is selected press Ctrl-C to copy it into the windows clipboard. Click on a new sheet and press Ctrl-V to paste a copy of sheet 1 to this new sheet.

Now run my macro on this new sheet. I've fixed the macro so it works better now. You were right that I was off a few rows. When the macro is complete, sort the data by Distance. This will put all the blank cells at the bottom. Delete the rows that have blank distance in them. They will all be towards the bottom of the table. Then make a pivot table from the remaining data. There are lots of possible ways to drag fields to a pivot table. I don't know exactly which will show what you want. BUT the data should now be correct.

See the attached with corrected VBA code and a second sheet where I've run the macro on and created a test pivot table.

[mcnamc2]

I am smiling ear to ear because IT WORKED! The data was right.

Glad you were able to catch the error, which should hopefully explain why I was so goshdarn confused!

You have no idea, you really helped me out! Thank you so much! Hope your knees get better!

Catharine