## Here's one for you Excel buffs

europa
Posts: 7334
Joined: Sun Jul 16, 2006 10:51 am
Location: southern end of Adelaide - home of hills, fixies and drop bears

### Here's one for you Excel buffs

My training log - 'tis now huge with a wealth of useless and irrelevant infomation - it's GREAT

A subset of said training log - the ride to school with my son and then my return each day. It's a 25km round trip with a heavy collection of hills. More to the point, I do the same trip every day (well ... most days), and so can compare my trips.

I thought it would be neat to have a graph showing, for each trip, the time, ave speed, ave HR and calories. It'd be neater still to have all these on the same graph so all you can see where changes are - oh, my ave speed was 20 km/hr faster but my HR went through the roof and is it possible to burn that many calories?

Problem, well, it's proving to be a problem for me because I don't really know what I'm doing with Excel. I can create a chart with these four things plotted, but the scale of the y axis assumes that every series being plotted is using the same units. When you consider that I'm trying to track changes in time of a minute or so, changes in speed of one km/hr or so, on a graph with a scale that incorporates the calorie burn of 1,000, you wind up being unable to see the changes in the lower figures.

Is it possible (and if so, HOW), to have a single chart, that has four series running across it, but have each series plotted to it's own units?

Richard

tuco
Posts: 2016
Joined: Wed Oct 11, 2006 3:36 pm
Richard,

I had the same problem so I enter the calories divided by 10 so today's 943 calories is entered as 94.3. It then fits neatly below the average HR and above the time (usually about 60 minutes).

There maybe a proper way around it but the above way is an easy solution.

europa
Posts: 7334
Joined: Sun Jul 16, 2006 10:51 am
Location: southern end of Adelaide - home of hills, fixies and drop bears
Thanks Tuco - I had considered fiddling the numbers but hadn't considered it very far - dividing by ten is a very neat solution. Thanks.

Richard

MichaelB
Posts: 9324
Joined: Tue Feb 20, 2007 9:29 am
Location: Adelaide, South Australia
You can plot some points using a secondary "Y axis".

E.g. I have total km travelled on the left Y axis, and then have avg speed, distance, etc on the second y axis.

I'd send/posta sample of the chart I use, but don't know how to attach it.

If you send me your email addy, I can email the one I use and then you can work it out or just dodge it from mine

Cheers

Michael B

europa
Posts: 7334
Joined: Sun Jul 16, 2006 10:51 am
Location: southern end of Adelaide - home of hills, fixies and drop bears
And just to scare myself and my son, and work out why the northwards run is so darned tiring, I fed the route into bikely. Here's the profile for the run from Home to School. It's public streets until the Expressway, then we follow a bikey until the end of the Expressway, then a main road to school. Of course, the lad then gets off his bike and goes in to talk to his ratbag mates whereas I have to turn round and do the rotten thing in reverse.

This profile runs along the Expressway heading north. We've always wondered why we find that northwards run harder than the southerly run - well, this shows why. The hill I call Expressway Hill, is the southerly run up to that crest - it's long. The northerly approach to that crest is shorter but steeper - there are a few downwards runs that don't show clearly at this scale - these allow you to rest and build up momentum but the individual climbs are pretty steep.

So that's half of my morning 25km run ('cause I turn round and come back along it). No wonder I'm a bit spacey when I get home

Red uphill, green downhill

Richard
Last edited by europa on Thu May 17, 2007 4:56 pm, edited 1 time in total.

europa
Posts: 7334
Joined: Sun Jul 16, 2006 10:51 am
Location: southern end of Adelaide - home of hills, fixies and drop bears
MichaelB wrote:You can plot some points using a secondary "Y axis".

E.g. I have total km travelled on the left Y axis, and then have avg speed, distance, etc on the second y axis.

I'd send/posta sample of the chart I use, but don't know how to attach it.

If you send me your email addy, I can email the one I use and then you can work it out or just dodge it from mine

Cheers

Michael B

Thanks mate. My email addy is shown on each post.

Richard

MichaelB
Posts: 9324
Joined: Tue Feb 20, 2007 9:29 am
Location: Adelaide, South Australia
europa wrote:
Thanks mate. My email addy is shown on each post.

Richard

I don't have glasses but maybe I need to - I can see a PM, Web addy but no email ??

europa
Posts: 7334
Joined: Sun Jul 16, 2006 10:51 am
Location: southern end of Adelaide - home of hills, fixies and drop bears
MichaelB wrote:
europa wrote:
Thanks mate. My email addy is shown on each post.

Richard

I don't have glasses but maybe I need to - I can see a PM, Web addy but no email ??

It's a little icon like this

Richard

MichaelB
Posts: 9324
Joined: Tue Feb 20, 2007 9:29 am
Location: Adelaide, South Australia
europa wrote:
MichaelB wrote:
europa wrote:
Thanks mate. My email addy is shown on each post.

Richard

I don't have glasses but maybe I need to - I can see a PM, Web addy but no email ??

It's a little icon like this

Richard

Me no see that one - just the profile, www and PM icons !!!

europa
Posts: 7334
Joined: Sun Jul 16, 2006 10:51 am
Location: southern end of Adelaide - home of hills, fixies and drop bears
It's there. There's one on your posts too. Can you see that? Maybe it's a feature just for those of us that wear tin undies (moderators)

Richard

europa
Posts: 7334
Joined: Sun Jul 16, 2006 10:51 am
Location: southern end of Adelaide - home of hills, fixies and drop bears
Just been poking around on the Admin board - Christopher's got email turned off so I'm guessing I'm seeing it because of my higher level of priviledges. Dat's cool. Check your PM's ... in a minute or so after I've sent it of course

Richard

heavymetal
Super Mod
Posts: 1381
Joined: Tue Sep 27, 2005 5:32 pm
It's a tin undies thing only

Other people can only see it, if you have it turned on in your profile.

Kev.
There is only one BicycleWA.

europa
Posts: 7334
Joined: Sun Jul 16, 2006 10:51 am
Location: southern end of Adelaide - home of hills, fixies and drop bears
FIXED ... in my profile - you should all be able to see it now. Yet another switch I hadn't noticed

Only brave people leave me alone with a form to fill in ... and that includes electronic ones

Richard

heavymetal
Super Mod
Posts: 1381
Joined: Tue Sep 27, 2005 5:32 pm
Just be aware that if you turn it on in your profile, the Google bot and other bots can see it. It can leave you open to spam.

Kev.

europa
Posts: 7334
Joined: Sun Jul 16, 2006 10:51 am
Location: southern end of Adelaide - home of hills, fixies and drop bears
heavymetal wrote:Just be aware that if you turn it on in your profile, the Google bot and other bots can see it. It can leave you open to spam.

Kev.

Mate, my addy is so open, putting it up here isn't going to make any difference.

Richard

mikesbytes
Super Mod
Posts: 19289
Joined: Thu Dec 01, 2005 11:42 pm
Location: Tempe, Sydney
Contact:

### Re: Here's one for you Excel buffs

europa wrote:My training log - 'tis now huge with a wealth of useless and irrelevant infomation - it's GREAT

A subset of said training log - the ride to school with my son and then my return each day. It's a 25km round trip with a heavy collection of hills. More to the point, I do the same trip every day (well ... most days), and so can compare my trips.

I thought it would be neat to have a graph showing, for each trip, the time, ave speed, ave HR and calories. It'd be neater still to have all these on the same graph so all you can see where changes are - oh, my ave speed was 20 km/hr faster but my HR went through the roof and is it possible to burn that many calories?

Problem, well, it's proving to be a problem for me because I don't really know what I'm doing with Excel. I can create a chart with these four things plotted, but the scale of the y axis assumes that every series being plotted is using the same units. When you consider that I'm trying to track changes in time of a minute or so, changes in speed of one km/hr or so, on a graph with a scale that incorporates the calorie burn of 1,000, you wind up being unable to see the changes in the lower figures.

Is it possible (and if so, HOW), to have a single chart, that has four series running across it, but have each series plotted to it's own units?

Richard

So a thread about your training log and there's no link to it. What time is the chashers war on everything on?

_________________________________________________________________________________
Burn plenty of Glycogen
Frame Size Calculator.....Park Tools Repair Guides Frame Size Calculator.....Rolling Resistance.....Rolling Performance.....Sheldon Brown's Bicycle Technical Info
training log.....Body-Mass Index, Waist-to-Height Ratio, Basal Metaboic Rate
Bicycle FAQs.....Bicycle Safety.....Cadence in Cycling.....Types of Bicycles
If the R-1 rule is broken, what happens to N+1?

ShanDog
Posts: 664
Joined: Fri Oct 20, 2006 6:03 pm
Location: Sydney - City
hey Richard,

I remember having to do something like this for uni. I found this resource to be really helpful... check it out

Unfortunately, as MichaelB says, you can only plot one extra data series on there using another y-axis on the right hand side.

Shannon

europa
Posts: 7334
Joined: Sun Jul 16, 2006 10:51 am
Location: southern end of Adelaide - home of hills, fixies and drop bears
sbra9635 wrote:hey Richard,

I remember having to do something like this for uni. I found this resource to be really helpful... check it out

Unfortunately, as MichaelB says, you can only plot one extra data series on there using another y-axis on the right hand side.

Shannon

Yikes
Something to work through - thanks mate.

Richard

LuckyPierre
Posts: 1432
Joined: Fri Jul 14, 2006 4:37 pm
Location: Canberra, ACT

### Re: Here's one for you Excel buffs

mikesbytes wrote: ... What time is the chashers war on everything on? ...

Yesterday (well, Wednesday, really), 9:30pm

Kev365428
Moderator
Posts: 2269
Joined: Mon Mar 19, 2007 8:52 am
Location: Ingleburn, NSW
Contact:
I'm about halfway through making a Access database to record all my log data.
Even adding in a maintenance log that will track maintenance events and when my next maintenance is due based on km ridden or time - whichever comes first.

Then comes the graphs......

Boy, you'de think I'd have had enough of this stuff after 8 hours at work.

Kev.

europa
Posts: 7334
Joined: Sun Jul 16, 2006 10:51 am
Location: southern end of Adelaide - home of hills, fixies and drop bears
You can have Access - it's given me too many ulcers over the years, though once again, I've tried to teach myself. It definitely comes under the heading of 'if you can do it, you're either a professional, nuts or both'

Richard

Kev365428
Moderator
Posts: 2269
Joined: Mon Mar 19, 2007 8:52 am
Location: Ingleburn, NSW
Contact:
Just got a hold of MS Office 2007.

Typical Microsoft, all of the good old stuff has been replaced by good (and sometimes not-so-good) new stuff, which means re-learning all that I've forgotten over the past 10 years or so.

Oh, I'm not a professional, so I guess I'm just nuts.

ShanDog
Posts: 664
Joined: Fri Oct 20, 2006 6:03 pm
Location: Sydney - City
Yeah I have 2007 as well. It's as if you suddenly have become computer illiterate again. Was such a maze the first time I used it. I find outlook 2007 is painfully slow too.

tuco
Posts: 2016
Joined: Wed Oct 11, 2006 3:36 pm
Kev365428 wrote:Just got a hold of MS Office 2007.

Typical Microsoft, all of the good old stuff has been replaced by good (and sometimes not-so-good) new stuff, which means re-learning all that I've forgotten over the past 10 years or so.

Oh, I'm not a professional, so I guess I'm just nuts.

Don't you hate so called up grades!

I use an Access DB to run my store for second purchases/sales and general sales and I wrote a VB front end to make data entry easy, to connect to my suppliers excel order forms and to allow capturing pictures of suspicious characters. The cops loved it.

Are you going to use VBA or do you need a front end for it?

Kev365428
Moderator
Posts: 2269
Joined: Mon Mar 19, 2007 8:52 am
Location: Ingleburn, NSW
Contact:
Tuco, I use a little code, but mainly the front end stuff provided by Access.

I have to admit that Access 2007 is leaps and bounds ahead when it comes to creating new forms, tabels etc than Access '03 ever was. My main gripe is trying to find where all the database tools are now hidden.

I guess it just a case of having an open mind to a new format.

Oh, the other thing is that '07 uses a totally new file format, that '03 doesn't support. Bummer.

Kev.

Return to “General Cycling Discussion”

### Who is online

Users browsing this forum: P!N20