MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
Excel help needed – I realise this is very simple for someone who knows what they are doing but I can’t do it!
For a long ride we are doing across the Alps, I have put in a table of places, distances (Km) and altitudes (m). I have (guessing from the map) catergorised the sections as road (meaning tarmac), jeep (meaning forest road or rough track) or path (meaning singletrack or what we might call a footpath). I have a weighting, currently 15/65/100 for these tracks to give ‘effort’. I also weight climbing or descending currently 9/1 and by multiplying each section distance*altitude*surface*climb or descend then you get a figure.
Once the sheet has been changed or made right so that it works then I might need to add in a small number of sections, measure the Km more accurately etc but that should all sort itself out when editing the data, or by changing the 15/65/100 ratio etc..
What I need is;
1 – to work out the effort value using the lookup function. I can’t seem to get it to work correctly at all.
2 – have a box where you can put in the number of days (say 6 or 7 or 8) and it should tell you where the overnights should be to make each day a similar amount of effort.
3 – draw a graph of height against distance and label it up with say every place or a few places whatever looks best.
I have the sheet here with me (at work), is there anyone who would be prepared to receive it by email and take a look?
Ta
Chris
There's planning, and then there's slightly too much obsessing...
Assuming your category is in column B, then probably the easiest way to give you an effort value for column C would be to enter this formula in all the cells in column C:
=IF(B1="Road",15,IF(B1="Jeep",65,100))
This assumes that there are only three possible classifications, i.e. if it's not road or jeep it must be path. You could equally replace the 15,65,100 values with cell references (make sure you "fix" them, i.e. $A$1) so that you can tweak the values later more easily.
thank you, sorted that, just the graph to do....