Getting Excel/Google Sheets to work out railway distances

The_Train

Established Member
Joined
2 Jun 2018
Messages
2,394
Wasn't sure where to post this but thought this section might be where others who use spreadsheets for their outings are hiding :)

I'm currently devising a 'one stop shop' database for myself and I've hit a bit of a problem with regards to the haulage aspect of it. I wasnt going to bother with haulage as it's not a priority of mine but I do record my mileage per loco/unit so thought it best to include it now save trying to add it in later. Basically I'm looking to include a 'Haulage' tab where I manually input the number of the loco/unit along with the miles accumulated on that trip (and other info but that's irrelevant to my problem - essentially a haulage log). Through a SUMIF formula this information is then allocated to that loco/unit within its own tab - e.g. if I had 3 separate instances of haulage behind 87002, the spreadsheet would work out the total mileage for those 3 outings and add that to the mileage column for 87002 I'd have in the Class 87 tab.

The problem I'm having is that I work out my mileage using the fantastic railmiles mileage engine which gives distance in miles and chains but getting a spreadsheet to understand that 1mi 69ch + 0mi 31ch = 2.20 and not 2.00 is proving difficult because spreadsheets automatically assume any decimal to be to 0.100=1 not 0.80=1.

I've found help elsewhere which has helped me come up with an array formula which works by modifying the decimal to 0.80. Unfortunately I've only been able to get this to work in its own right and I'm struggling to make this formula work alongside the SUMIF formula I mentioned earlier.

Hopefully this makes sense and any suggestions to solve this would be very much welcomed :)
 
Sponsor Post - registered members do not see these adverts; click here to register, or click here to log in
R

RailUK Forums

Kite159

Veteran Member
Joined
27 Jan 2014
Messages
14,043
Location
West of Andover
Have three columns, one for miles, one for chains and the third for miles+converted chains

For example journey from A to B is 2 miles and 20 chains, first column will be 2, 2nd column 20, 3rd column 2.25. Then use the 3rd column as the basis for your mileage calculations

If that makes sense
 

The_Train

Established Member
Joined
2 Jun 2018
Messages
2,394
Have three columns, one for miles, one for chains and the third for miles+converted chains

For example journey from A to B is 2 miles and 20 chains, first column will be 2, 2nd column 20, 3rd column 2.25. Then use the 3rd column as the basis for your mileage calculations

If that makes sense
I think I see where you're going with it but why would the 3rd column equate to 2.25 if the chains in question is 20? (Or am I picking up on a typo there?)
 

Kite159

Veteran Member
Joined
27 Jan 2014
Messages
14,043
Location
West of Andover
I think I see where you're going with it but why would the 3rd column equate to 2.25 if the chains in question is 20? (Or am I picking up on a typo there?)
80 chains in a mile, so 1 chain = 0.0125 mile
20 chains = 0.25 miles

Basically it will change the data from "imperial" to metric

-----

I've attached a screenshot of my mileage sheet if that helps, although the data is cut down as I have other sheets for my main travels log.
 

Attachments

Last edited:

The_Train

Established Member
Joined
2 Jun 2018
Messages
2,394
80 chains in a mile, so 1 chain = 0.0125 mile
20 chains = 0.25 miles

Basically it will change the data from "imperial" to metric

-----

I've attached a screenshot of my mileage sheet if that helps, although the data is cut down as I have other sheets for my main travels log.
Ah that makes sense and would probably have been an easier route to take. However, I think that I've just solved the problem which will allow me to show 1.79 to mean 1mi 79ch and for my spreadsheet to be able to add 1.79 and 0.21 together and come up with an answer of 2.20 without the need to convert into metric.

I suppose that the talk of imperial and metric leads to the question of whether rail mileage will eventually move away from chains and whether I'd need to be converting to metric I'm the future anyway?
 

Shaw S Hunter

Established Member
Joined
21 Apr 2016
Messages
2,231
Location
Sunny South Lancs
Ah that makes sense and would probably have been an easier route to take. However, I think that I've just solved the problem which will allow me to show 1.79 to mean 1mi 79ch and for my spreadsheet to be able to add 1.79 and 0.21 together and come up with an answer of 2.20 without the need to convert into metric.

I suppose that the talk of imperial and metric leads to the question of whether rail mileage will eventually move away from chains and whether I'd need to be converting to metric I'm the future anyway?
In theory Network Rail is already moving, if rather slowly, towards all-metric measurements; widespread introduction of ETCS may well provide the impetus to accelerate this process.

As an aside I would point out that a chain is 22 yards is 21.12 metres or roughly the same as the length of a typical rail passenger car. Given the variability in train lengths and stopping points, as well as a degree of uncertainty about the precise measuring point for individual stations, it is somewhat questionable whether enthusiast haulage records are worth recording to a chain level degree of accuracy. Tenths of a mile have always seemed perfectly acceptable to me as they are to motorists!
 
Last edited:

AJM580

Member
Joined
31 Jan 2016
Messages
441
Location
Norwich
To make my life easier, I round miles and chains to the nearest 0.05 of a mile. This gives a +/- of 2 chains or 44 metres on distances but works well for train timing and mileages.
 

telstarbox

Established Member
Joined
23 Jul 2010
Messages
4,766
Location
Wennington Crossovers
Unless you do a lot of very short trips, I would just have a miles column and round the chains up or down to the nearest mile. Over the long run the 'overs and unders' will cancel out so the total will be accurate.
 

xotGD

Established Member
Joined
4 Feb 2017
Messages
2,921
Unless you do a lot of very short trips, I would just have a miles column and round the chains up or down to the nearest mile. Over the long run the 'overs and unders' will cancel out so the total will be accurate.
I have to agree that recording to the level of the chain is way too precise. For example, arriving from Airedale into 4B at Leeds is quite a bit shorter than going all the way down to 8A.

I used to record to the nearest quarter mile when I did such things.
 

ash39

Established Member
Joined
8 Feb 2012
Messages
1,378
I just convert all mileages (taken from the excellent Railmiles site) to decimals. Makes it so much simpler.

Eg:

City Thameslink to Blackfriars (14 chains) would go in as 0.18 miles.

I accept the argument that where you stand on the train and the platforms your train use can have a substantial effect on the 'actual' distance travelled, but at least doing it this way has a finite 'answer' for every scenario.
 

Snow1964

Member
Joined
7 Oct 2019
Messages
326
Location
Kingston upon Thames
I just convert all mileages (taken from the excellent Railmiles site) to decimals. Makes it so much simpler.

Eg:

City Thameslink to Blackfriars (14 chains) would go in as 0.18 miles.

I accept the argument that where you stand on the train and the platforms your train use can have a substantial effect on the 'actual' distance travelled, but at least doing it this way has a finite 'answer' for every scenario.
Your distance travelled on the train does not change depending on where you are in the train, as whole train moves same distance.
It will vary if you walk along a train and exit from a different door.

What will change is how far you walk along platforms, but isn’t the Op measuring train travel distance, not how far he has strolled around platforms to get to/from a train
 

Shaw S Hunter

Established Member
Joined
21 Apr 2016
Messages
2,231
Location
Sunny South Lancs
Your distance travelled on the train does not change depending on where you are in the train, as whole train moves same distance.
It will vary if you walk along a train and exit from a different door.

What will change is how far you walk along platforms, but isn’t the Op measuring train travel distance, not how far he has strolled around platforms to get to/from a train
As I mentioned upthread stopping points aren't necessarily consistent. Consider a Pacer from Colne terminating at Preston; quite a difference between arriving in one of the south end bays and the north end of one of the through platforms. Or the difference between the main shed at Piccadilly compared to the P13/14 island. Or stations where several trains can be "stacked". How many haulage fans attempt to make adjustments for such variances? Much simpler to be a little less fussy about precision because ultimately what difference does it make?!
 

The_Train

Established Member
Joined
2 Jun 2018
Messages
2,394
I seem to have opened an interesting debate on railway measurements and how those chasing haulage actually do it. I think the obvious answer is similar to a lot with this hobby - there is no right and wrong way and it's all down to preferences.

I imagine there are people who have a need to be very specific about it but without some very high tech measuring devices I'm not sure that's possible. I basically use railmiles, input the start and end point and (providing the route looks about right) take the miles and chains it throws back at me as read. It goes along the lines of what @telstarbox said in that any that I'm 'officially' out on will correct themselves over time by others being up or down.

Back onto the miles or miles and and chains discussion, this might just be me being stupid (because I am) but what exactly does the .1 in 1.1 miles actually stand for? I mean, if we say 1.5km we instantly understand the .5 to mean 500 metres and 1.5 litres the .5 Is 500 mililitres but the .1 in 1.1 miles actually equates to 160 metres. So is the .1 in 1.1 miles just a tenth of a mile?
 

Shaw S Hunter

Established Member
Joined
21 Apr 2016
Messages
2,231
Location
Sunny South Lancs
I seem to have opened an interesting debate on railway measurements and how those chasing haulage actually do it. I think the obvious answer is similar to a lot with this hobby - there is no right and wrong way and it's all down to preferences.
So many hobbies that this applies to! It's amazing how many people seem unable to accept this.

I imagine there are people who have a need to be very specific about it but without some very high tech measuring devices I'm not sure that's possible. I basically use railmiles, input the start and end point and (providing the route looks about right) take the miles and chains it throws back at me as read. It goes along the lines of what @telstarbox said in that any that I'm 'officially' out on will correct themselves over time by others being up or down.
It's in the nature of the hobby that some of its practitioners are somewhat preoccupied with details and specifics. My preference, back in the day, was to refer to the collection of "Miles and Chains" booklets though these days the Quail books are a very good printed reference.

Back onto the miles or miles and and chains discussion, this might just be me being stupid (because I am) but what exactly does the .1 in 1.1 miles actually stand for? I mean, if we say 1.5km we instantly understand the .5 to mean 500 metres and 1.5 litres the .5 Is 500 mililitres but the .1 in 1.1 miles actually equates to 160 metres. So is the .1 in 1.1 miles just a tenth of a mile?
How about 1.1 mile = 1m08ch? Given that only the relatively old would now have an issue with recognising metric notations it makes sense to use a non-metric notation in any situation where non metric units are being used eg 2.5 pounds in weight should be expressed as 2lbs8oz. The logical conclusion to this is that we shouldn't be using miles at all! However they are so deeply ingrained into our culture I can't see us doing like Australia and just ditching imperial measurements and moving on, especially not with Brexit fever around.
 

adrock1976

Established Member
Joined
10 Dec 2013
Messages
2,837
Location
What's it called? It's called Cumbernauld
During my first spell of recording rail journeys between 1988 to 1994, I used the big Great Britain Passenger Railway Timetable book to work out mileages. There was a sentence in the book that mileages were rounded to the nearest quarter mile, which I have always based my method of recording mileages on.

If you think about it, it makes sense as along the various railway lines in Great Britain, there are quarter, half, and three-quarter mileposts in between the whole numbers in most places.
 

Jamesrob637

Established Member
Joined
12 Aug 2016
Messages
1,856
Is there a thread where someone/something will work out how many miles I have travelled in a day? If on a day ranger, I note down which trains I took (services not unit numbers: not interested in that).
 

The_Train

Established Member
Joined
2 Jun 2018
Messages
2,394
Is there a thread where someone/something will work out how many miles I have travelled in a day? If on a day ranger, I note down which trains I took (services not unit numbers: not interested in that).
I use http://rmme.railmiles.me

If you're making multiple journeys you have to stick them in separately and then work out a total mileage (well that's how I do it anyway)
 

king1030

New Member
Joined
2 Oct 2019
Messages
2
Location
Taunton
Hi The Train,

I also use miles and chains and would you be willing to pass on your formula for excel calculation please?

I use lots of paper calculating it lol

Best wishes
 

Bevan Price

Established Member
Joined
22 Apr 2010
Messages
4,875
One thing to consider for those aiming for 100% accuracy is that if you are travelling between two terminal stations, you have not travelled the distance between the buffer stops at each end - you need to subtract a figure related to the length of the train
 

xotGD

Established Member
Joined
4 Feb 2017
Messages
2,921
One thing to consider for those aiming for 100% accuracy is that if you are travelling between two terminal stations, you have not travelled the distance between the buffer stops at each end - you need to subtract a figure related to the length of the train
And if the departure and/or arrival platforms are already occupied, and your train is stacked, the distance will be less again.

Honestly, stating mileage to the nearest chain is a victory of precision over accuracy.
 

Kite159

Veteran Member
Joined
27 Jan 2014
Messages
14,043
Location
West of Andover
And if the departure and/or arrival platforms are already occupied, and your train is stacked, the distance will be less again.

Honestly, stating mileage to the nearest chain is a victory of precision over accuracy.
And the distance can vary depending how long the train is at certain stations (where trains ranging from 2 coaches to 10 coaches can operate)
 

king1030

New Member
Joined
2 Oct 2019
Messages
2
Location
Taunton
I've attached a screenshot of how i tot up how many miles I've travelled on my spreadsheet. I autosum the miles column and the chains column then use the formula =SUM(F13+(G13/80)) in the Total Miles bit. Should add that I get the mileages from http://rmme.railmiles.me/ in the first place.
Hi Bear Hugger

Thank you for your reply and I'll take a look. What I wanted to do was have a progressive total after each haulage was logged.

Best wishes

Andy
 

The_Train

Established Member
Joined
2 Jun 2018
Messages
2,394
A quick note about the RailMiles site.

If you find it useful you should be aware that changes are afoot. Please read https://blog.realtimetrains.com/2020/01/railmiles-update-january-2020/ for further information.
Thanks for posting that.
I guess we won't know the full impact of how things may change until we know what the daily limit will be. Not sure I can really justify another railway based subscription so hopefully the limit is reasonable to suit a day out on the rails
 

Kite159

Veteran Member
Joined
27 Jan 2014
Messages
14,043
Location
West of Andover
Thanks for posting that.
I guess we won't know the full impact of how things may change until we know what the daily limit will be. Not sure I can really justify another railway based subscription so hopefully the limit is reasonable to suit a day out on the rails
Agreed, I tend to use it to work out where I need to take an unit to clear it for ten miles. One change from the old RTT is that the mileage column doesn't show on mobile devices, even in desktop mode.
 

Top