Here's one for you Excel buffs

User avatar
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

Postby europa » Thu May 17, 2007 4:08 pm

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

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

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

Postby tuco » Thu May 17, 2007 4:24 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.

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

Postby europa » Thu May 17, 2007 4:46 pm

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

User avatar
MichaelB
Posts: 14775
Joined: Tue Feb 20, 2007 9:29 am
Location: Adelaide, South Australia

Postby MichaelB » Thu May 17, 2007 4:49 pm

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

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

Postby europa » Thu May 17, 2007 4:52 pm

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 :D

Image
Red uphill, green downhill

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

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

Postby europa » Thu May 17, 2007 4:53 pm

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

User avatar
MichaelB
Posts: 14775
Joined: Tue Feb 20, 2007 9:29 am
Location: Adelaide, South Australia

Postby MichaelB » Thu May 17, 2007 4:57 pm

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 ?? 8)

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

Postby europa » Thu May 17, 2007 4:59 pm

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 ?? 8)
It's a little icon like this Image

Richard

User avatar
MichaelB
Posts: 14775
Joined: Tue Feb 20, 2007 9:29 am
Location: Adelaide, South Australia

Postby MichaelB » Thu May 17, 2007 5:01 pm

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 ?? 8)
It's a little icon like this Image

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

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

Postby europa » Thu May 17, 2007 5:03 pm

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 :D (moderators)

Richard

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

Postby europa » Thu May 17, 2007 5:09 pm

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 :roll:

Richard

heavymetal
Super Mod
Super Mod
Posts: 1381
Joined: Tue Sep 27, 2005 5:32 pm

Postby heavymetal » Thu May 17, 2007 5:10 pm

It's a tin undies thing only :D

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

Kev.
There is only one BicycleWA.

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

Postby europa » Thu May 17, 2007 5:18 pm

FIXED ... in my profile - you should all be able to see it now. Yet another switch I hadn't noticed :roll:

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

Richard

heavymetal
Super Mod
Super Mod
Posts: 1381
Joined: Tue Sep 27, 2005 5:32 pm

Postby heavymetal » Thu May 17, 2007 6:22 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.

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

Postby europa » Thu May 17, 2007 6:29 pm

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

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

Re: Here's one for you Excel buffs

Postby mikesbytes » Thu May 17, 2007 9:00 pm

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

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?

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

Postby ShanDog » Thu May 17, 2007 9:42 pm

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

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

Postby europa » Thu May 17, 2007 10:00 pm

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 :shock:
Something to work through - thanks mate.

Richard

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

Re: Here's one for you Excel buffs

Postby LuckyPierre » Fri May 18, 2007 9:07 am

mikesbytes wrote: ... What time is the chashers war on everything on? ...
Yesterday (well, Wednesday, really), 9:30pm :?

User avatar
Kev365428
Moderator
Moderator
Posts: 2289
Joined: Mon Mar 19, 2007 8:52 am
Location: Ingleburn, NSW

Postby Kev365428 » Fri May 18, 2007 9:13 am

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.

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

Postby europa » Fri May 18, 2007 9:15 am

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' :D

Richard

User avatar
Kev365428
Moderator
Moderator
Posts: 2289
Joined: Mon Mar 19, 2007 8:52 am
Location: Ingleburn, NSW

Postby Kev365428 » Fri May 18, 2007 9:20 am

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.

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

Postby ShanDog » Fri May 18, 2007 9:27 am

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.

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

Postby tuco » Fri May 18, 2007 9:29 am

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?

User avatar
Kev365428
Moderator
Moderator
Posts: 2289
Joined: Mon Mar 19, 2007 8:52 am
Location: Ingleburn, NSW

Postby Kev365428 » Fri May 18, 2007 11:02 am

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.

Who is online

Users browsing this forum: No registered users