Anyone good with Excell?

Barks

Barks

Barks

Soldato
Joined
20 Jul 2008
Posts
4,462
Hi,

I've been tracking my fitness since January. I've been doing a 2.6 mile run every few days and timing myself. I've now got plenty of data but I'm not sure the best way to represent it in Excell.

I want a nice graph with dates that plots a line of best fit as my fitness improves, but I'm struggling to do this.

At first I did the following sort of thing:
1st January 2009 - 18.46
7th January 2009 - 19
8th January 2009 - 19.2
9th January 2009 - 18.8
Which gave me a horrible graph where the computer failed to realise there are time gaps between the dates.

I then had to do this:
1st January 2009 - 18.46
2nd January 2009 - 0
3rd January 2009 - 0
4th January 2009 - 0
5th January 2009 - 0
6th January 2009 - 0
7th January 2009 - 19
8th January 2009 - 19.2
9th January 2009 - 18.8

So is there anyway of letting Excell know there are time gapes without having to put in 0 on the dates I didn't run.

Secondly is there an effective way of recording time without having to convert the seconds into a decimal. At the moment I have to type in
=16.0+(35/60) to get my value.

Would really appreciate some help,

Cheers
 
Is it something like this that you are after?

75789253.jpg
 
Right click on the chart you have already created, go to the 'Axes' tab and set it to 'Category' rather than automatic.

That should then remove the need for you to included those dates where your not doing your run.
 
scott212 said:
Secondly is there an effective way of recording time without having to convert the seconds into a decimal. At the moment I have to type in
=16.0+(35/60) to get my value.
Click to expand...

For this part, I would just assign a custom number format of [hh]:mm to the range where your times are. (This is actually hours and minutes but it will allow you to type '19:32' into the cell and it'll be fine for plottingon your graph)
 
Hi,

Thanks for the help so far. I've managed to get the cells to recognise the times in the [hh]:mm format you suggested, cheers for that.

I'm struggling to get the graph working. For starters I can't set the axis so that it reads the dates. Am I using the wrong type of graph for this?



Cheers guys
 
Yes, I would use a 2d line chart. Select the chart, click on "Design" Tab and select the "Change Chart Type" menu and have a browse through them.

EDIT: Sorry, didn't read your question properly. Format the data in column A to "Date". The graph should then recognise it as dates.
 
Last edited:
Whats wrong with the graph in the last post? Thats how I would plot it.

Do you have an example of how you'd like it to look?
 
Hey man, I uploaded what I would do so you could have a look at it, you can always add more dates or copy it for the next month. Let me know what you think.

I formatted it so the line goes "up" with improvements - just looks better I think.

CLICKY
 
Meeko said:
Whats wrong with the graph in the last post? Thats how I would plot it.

Do you have an example of how you'd like it to look?
Click to expand...

A line of best fit is not accurate unless all the data points are relative. In this case the distance measured between them is time and it's no use if this is not incorporated. It would be fine if I did my run every two days, but the distance between each point varies as I can only do the runs when I have spare time which varies around my student lifestyle :D

Spitz just downloading your file now thanks
 
Last edited:
spitzoid said:
Hey man, I uploaded what I would do so you could have a look at it, you can always add more dates or copy it for the next month. Let me know what you think.

I formatted it so the line goes "up" with improvements - just looks better I think.

CLICKY
Click to expand...

That's great, so I take it you do have to put in the dates where you don't run. I was hoping maybe Excel had a feature that did this for you?

I agree it does look better going up.

Cheers
 
No you don't have to put the dates in where you don't run. However, because column A is in "Date" format the chart will show all the dates in between. The only way around this if you want the chart to only show the specific dates on which you run is to format column A as "Text", then the chart will only show the dates that you enter in column A. Depends if you can be bothered with all that typing I guess.

For me the easiest thing to do though is just to make a list of dates in column A (say 01/01/09-31/12/09 or whatever your data covers) and then just enter a time on the days you do run.
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom