Line graph help

[SublimeLB]

Hi,

I'm borderline helpless with Excel, but would like to graph my workout routines to help me track progress and plateaus. The current spreadsheet I'm using has each "set" of workouts in one cell in the format of 'weightXreps' (e.g. 100x12 which would be 100lbs lifting 12 times in that set). I have entries like that each day I do those sets for that specific excersize. What I'm looking to do is create a dual-line graph that will show progress over time for both weight and reps (one line for each). I'm not sure how to tell Excel to graph the first number before the "X" and then graph the number after the "X" specifically. Is there a different seperator I can use? Or perhaps there's a much easier way to approach this whole thing? I'd really like to keep the weight and reps in the same cell, otherwise the spreadsheet will become very large.

Thanks!
SLB

[NickyC]

Hi

this is not going to be easy if you have two values in the same cell, separated with "x". Excel will treat the cell's contents as a single string, not two separate numerical values, and will not be able to chart it.

It will be much easier if you have the date in the first column, the weight in the second and the reps in the third.

One very east easyway to split your data column into 2 columns is to use the "text to columns" feature. Make sure the column to the right of your data is blank. Select all the cells in your data column (with eg 100x12 in). On the data tab, in the data tools box select text to columns > delimited > and enter x next to the "other" option. This should split your data into two columns.

[SublimeLB]

Hi

this is not going to be easy if you have two values in the same cell, separated with "x". Excel will treat the cell's contents as a single string, not two separate numerical values, and will not be able to chart it.

It will be much easier if you have the date in the first column, the weight in the second and the reps in the third.

One very east easyway to split your data column into 2 columns is to use the "text to columns" feature. Make sure the column to the right of your data is blank. Select all the cells in your data column (with eg 100x12 in). On the data tab, in the data tools box select text to columns > delimited > and enter x next to the "other" option. This should split your data into two columns.

Thanks for the info. The text to columns option is useful for converting the data I already have, though I'll probably have to change the format of my spreadsheet. That's my real problem... I'm not sure how to put the data into the spreadsheet most efficently...

Here's an example of how I'm currently doing it. Any suggestions for how to re-arrange would be great!

\1

[NickyC]

ah - not quite as I imagined. I'm not sure what you are trying to chart. what would you expect to be on the axes?

[SublimeLB]

ah - not quite as I imagined. I'm not sure what you are trying to chart. what would you expect to be on the axes?

I would like to create a graph for each workout, each graph having two lines. One line shows progress in weight lifted, the other shows # of reps, over time. For example, a chart for "Incline Dumbell Press" (one of the workouts on that sheet) would have it's own chart. You'll notice I have 4 sets for that workout though, but I'm fine with just taking the data from one of those sets, since they're almost always identical anyways. So for that workout, I'd like a graph that shows two lines progressing over time (the dates at top), one for weight, one for reps. I track this information so I can more easily identify plateaus, since I'm supposed to cycle breaks and changes in routines when they occur. And of course because seeing progress gives me a reason to torture myself like this on a daily basis, lol.

[NickyC]

Is tihs something like what you want?

[SublimeLB]

Is tihs something like what you want?

That's pretty good! I could use that format I think. Is there a way to use two separate values for the Y axis? For example, have the weight graph use values 0-120, and reps chart values of 0-15?

Thanks again for your help Nicky

[NickyC]

Hi
you can use a double y axis - have one series on a left axis and one on a right
Right click on the series you want on the second axis, and select format data series > Secondary axis in the series options box