Author Topic: LEL magic spreadsheet (Read 9469 times)

vorsprung · « **on:** 05 February, 2009, 10:21:32 am »

I have modified a spreadsheet I found for one of the US 1200 BRM, the "Last Chance" to have the
controls etc for LEL and the numbers in km

This sheet lets you guess your average speed per section between controls, time wasted on the road, time at the control and then shows when you get to each place. If you are out of time it turns the control red, which is quite groovy.

You should be able to get it here ( if i've done the correct google voodoo )

http://spreadsheets.google.com/ccc?key=pgwkrs1Px75ut4lUr8WYLDA&hl=en_GB

mattc · « **Reply #1 on:** 05 February, 2009, 12:35:19 pm »

yep, URL works (folks will need a Google Docs login to make a copy i.e. to make their own changes ).

unfortunately;

Having copied it, the late colours don't appear, and
exporting to ODS doesn't open very well in Open Office.

So a partial success!

I'm new to Google Docs, so this may not be the Whole Truth ...

radelwombat · « **Reply #2 on:** 05 February, 2009, 10:32:44 pm »

wow

what am I going to do now with all the lists I jotted down on the backs of envelopes over the past months $:-\$

Thanks
Radelwombat

Panoramix · « **Reply #3 on:** 05 February, 2009, 11:55:42 pm »

That is cool!!!

And the Magic Spreadsheet tells me that I should sleep at washingborough, Alston, Alston again and Thurlby, do you know if these controls have good sleeping facilities?

Greenbank · « **Reply #4 on:** 06 February, 2009, 10:51:58 am »

Distances are a bit out, they should be:-

0, 65, 152, 218, 324, 414, 464, 541, 600, 719, 838, 897, 974, 1024, 1114, 1188, 1254, 1341, 1406

For Alston there *might* be the ability to control and sleep at Langdon Beck YH which is about 12 miles south of Alston. Not sure though obviously.

mattc · « **Reply #5 on:** 06 February, 2009, 12:28:27 pm »

So am I the only one who doesn't get the color coding? Browser issue?

Greenbank · « **Reply #6 on:** 06 February, 2009, 12:38:01 pm »

Quote from: mattc on 06 February, 2009, 12:28:27 pm

So am I the only one who doesn't get the color coding? Browser issue?

I just added a "Time In Hand Upon Departure" column which makes it much easier to read than any colour coding.

In Excel:
After doing a global search/replace of START_TIME_1200 with E6, and setting F6 to be equal to E6.
Set cell N7 to be equal to =F7-M7 and then drag/copy this to all other relevant cells in column N, then select them all and set the format to Custom -> [h]:mm:ss

mattc · « **Reply #7 on:** 06 February, 2009, 12:58:14 pm »

Quote from: Greenbank on 06 February, 2009, 12:38:01 pm

Quote from: mattc on 06 February, 2009, 12:28:27 pm
So am I the only one who doesn't get the color coding? Browser issue?

I just added a "Time In Hand Upon Departure" column which makes it much easier to read than any colour coding.

That sounds sensible - and I'll hide the "Control Open" field while I'm there

Reading the Help, one cannot format cells using references in Google Docs, so I'm mystified how Vorsprung thought this worked - perhaps such things got lost when he imported an Excel file? I am very trusting, so if you give me something broken I will assume I'm using it wrong!

(I really hate date-time manipulation, so tips always welcome)

Bianchi Boy · « **Reply #8 on:** 07 February, 2009, 09:15:29 am »

I have downloaded the sheet and I now know the size of the task ahead. Oh dear!

Hummers · « **Reply #9 on:** 07 February, 2009, 10:02:20 am »

Hmmmm.

The magic spreadsheet tells me that I should go out on the club run tomorrow and not do any DIY around the house.

I will put this to Mrs H and offer the magic spreadsheet up as the oracle.

H

mattc · « **Reply #10 on:** 07 February, 2009, 12:23:08 pm »

Arrivee has given me some solace by printing some date/time nonsense;
P.13 lists Gamlingay (return) as closing at

0.322916667

Hummers · « **Reply #11 on:** 07 February, 2009, 05:06:04 pm »

Hmmmm.

Mrs H tells me that should I listen to the magic spreadsheet again, she will break my legs.

All three of them.

H

Panoramix · « **Reply #12 on:** 07 February, 2009, 07:13:26 pm »

Quote from: Hummers on 07 February, 2009, 10:02:20 am

Hmmmm.

The magic spreadsheet tells me that I should go out on the club run tomorrow and not do any DIY around the house.

I will put this to Mrs H and offer the magic spreadsheet up as the oracle.

H

Quote from: Hummers on 07 February, 2009, 05:06:04 pm

Hmmmm.

Mrs H tells me that should I listen to the magic spreadsheet again, she will break my legs.

All three of them.

H

I shan't listen too much to the magic spreadsheet as the receivers had to be called after Hummer lost 1 arm after skidding on an icy club run + 3 legs at home. An arm and a leg are pricey enough but an arm and 3 legs were just too many.

vorsprung · « **Reply #13 on:** 09 February, 2009, 09:35:10 am »

I've made the distances in line with greenbanks more accurate suggestions

The Mechanic · « **Reply #14 on:** 09 February, 2009, 10:07:50 am »

Am I right in thinking that the spreadsheet is set up for a 1430 departure from Lee Valley. If so, what do I need to do to change it the the 0800 start.

Greenbank · « **Reply #15 on:** 09 February, 2009, 11:10:30 am »

Quote from: vorsprung on 09 February, 2009, 09:35:10 am

I've made the distances in line with greenbanks more accurate suggestions

FYI, they were taken from the 1st draft of the routesheets posted in the other thread.

It makes going for Coxwold on day 1 even more interesting.

vorsprung · « **Reply #16 on:** 09 February, 2009, 11:44:54 am »

Quote from: The Mechanic on 09 February, 2009, 10:07:50 am

Am I right in thinking that the spreadsheet is set up for a 1430 departure from Lee Valley. If so, what do I need to do to change it the the 0800 start.

Alter the date/time in boxes F6 and E6

simonp · « **Reply #17 on:** 09 February, 2009, 01:43:02 pm »

6 minutes at a control seems hopelessly optimistic, or is it just me?

chris · « **Reply #18 on:** 09 February, 2009, 01:48:35 pm »

Quote from: simonp on 09 February, 2009, 01:43:02 pm

6 minutes at a control seems hopelessly optimistic, or is it just me?

I'm sure that TG would describe that as excessive faffing

mattc · « **Reply #19 on:** 09 February, 2009, 01:49:02 pm »

Yes; but I assume it's appropriate for events with just a roadside stamp/swipe-n-go. This spreadsheet is unusual in having a "time stopped during leg" field - I'm guessing the owner expects to stop for food etc between controls. (I would only use this field for stops at friends/family, or for snowball fights in the Pennines).

This makes no sense on LEL, as we have already paid for food at every control (so let's all hope that queueing is minimal!)

Greenbank · « **Reply #20 on:** 09 February, 2009, 02:01:21 pm »

Quote from: simonp on 09 February, 2009, 01:43:02 pm

6 minutes at a control seems hopelessly optimistic, or is it just me?

*digs about in gpx file from London to Thorne ride last year*

15 minute p*n*t*r* stop outside Ashwell (Cheshunt to Gamlingay leg)
30 minute stop in Gamlingay (sandwich and drink from shop, ATM receipt, text update, bit of faff)
90 minute stop in Bourne (15 minutes wasted in first cafe before they told us they'd stopped doing hot food, friend feeling crap so we stopped for longer than expected, ATM receipt, text update)
20 minute stop in Lincoln (petrol station receipt, eat some Ginsters, text update, on the road again) doesn't include time spent ensuring broken friend got a room at the Premier Inn just before Lincoln

6 minutes at Gamlingay on the way out may be ok, it is just a stamp-and-go and water control after all, but that will leave you doing the first 152km on whatever you eat on the move.

I'm planning on taking a minimum of 45 minutes at each control *average* (not including the sleep stops where I'll obviously take longer).

I had a try keeping control times down on the Willy Warmer. I stopped for 25 minutes at 50km (including food), 20 minutes at 100km (food from shop rather than the busy looking cafe) and 50 minutes at 160km (Saino's cafe). More food on the move is my plan for LEL.

vorsprung · « **Reply #21 on:** 09 February, 2009, 03:55:08 pm »

Quote from: simonp on 09 February, 2009, 01:43:02 pm

6 minutes at a control seems hopelessly optimistic, or is it just me?

Yes 6 minutes is very optimistic

In the column for "break at control (hours)" I've only put 0.1 ie 6 minutes once. You can fill in whatever numbers you like though!

Also there is a column "Minutes off bike" which seems to be time spent faffing on the road. I take this to mean time spent looking at the route sheet, stopping to buy a sandwich, call of nature and the 101 other things that waste time

Greenbank · « **Reply #22 on:** 09 February, 2009, 04:03:38 pm »

Exactly, the other bit I forgot to add to mine was, even though I stopped for just 25+20+50 = 1h35 at controls on the Willy Warmer 200, I still managed to stop for a further 30 minutes out on the road (auto start/stop cycle computer recorded 9h09m of riding over an elapsed 11h15). I can account for a bit of this (a couple of 5 minute long stops I can remember) but the rest is just where the 30 or 60 seconds at lights just keep adding up.

mattc · « **Reply #23 on:** 09 February, 2009, 04:26:07 pm »

I know it's interesting analysing GPX logs etc, but for planning purposes, do we really care how many pee stops and red lights we will encounter on each leg? It's gotta be easier to just include this in the average speed, hasn't it?

I'm already in danger of going mad filling these things in! [Good thing I have to go to work from next Monday ... ]

vorsprung · « **Reply #24 on:** 09 February, 2009, 04:36:01 pm »

Quote from: mattc on 09 February, 2009, 04:26:07 pm

do we really care how many pee stops and red lights we will encounter on each leg?

No we don't
All the spreadsheet really tells you is how difficult it is to get under 100 hours, how little sleep you will get etc

A detailled plan will never come to pass- and you would already know from the distances that if you start at 2pm Sunday, by bedtime Monday you need to be at Alston or Cannonbie or thereabouts.