Adding Miles And Furlongs Using Excell

[Victor Vectis]

Good evening all.

I use an Excell spreadsheet to keep a log of our boats travels.

I take the distances travelled between stopping points from Canal Planner in miles and furlongs and add them up in my head........OK, on a scrap of paper.

I can't help thinking that I ought to be able to get Excell to do the adding up for me. What I'm looking for is a formula or routine that will add up the miles and the furlongs and when the furlong total reaches 8 carry that mile across to the miles column.

So that, for example, 2 miles 5.5 furlongs added to 7 miles 6.75 furlongs would come to 10 miles 4.25 furlongs.

 

Any ideas chaps and chapesses?

[john6767]

Why not just change your defaults in canalplan to use decimal miles.

[colinwilks]

Add the two figures together by converting both into furlongs (miles x 8) + furlongs. This result is your data column.

In the miles column have int(data column/8), gives miles as an integer.

In the separate furlongs column have data column - (miles column x 8) gives the remainder in furlongs.

 

Got to say I agree with John above though!

Edited October 1, 2015 by colinwilks

[Henhouse]

Good evening all.

 

I use an Excell spreadsheet to keep a log of our boats travels.

 

I take the distances travelled between stopping points from Canal Planner in miles and furlongs and add them up in my head........OK, on a scrap of paper.

 

I can't help thinking that I ought to be able to get Excell to do the adding up for me. What I'm looking for is a formula or routine that will add up the miles and the furlongs and when the furlong total reaches 8 carry that mile across to the miles column.

So that, for example, 2 miles 5.5 furlongs added to 7 miles 6.75 furlongs would come to 10 miles 4.25 furlongs.

 

Any ideas chaps and chapesses?

 

I take it you mean Five and a half, Six and three quarters, and Four and a quarter.

 

Decimal points and furlongs don't really fit together to well.

 

Time we got rid of the metric system, and went back to doing things properly!

[steven wilkinson]

how easy is the ten times table?

[Scholar Gypsy]

Here you go. (excel file)

 

 

The formula for (whole) miles is

 

=INT (SUM(miles)+SUM(furlongs)/8)

 

and the number of furlongs left over is calculated using the remainder or modulus function (often known as clockface arithmetic):

 

=MOD (SUM(furlongs),8)

Edited October 1, 2015 by Scholar Gypsy

[lostnortherner]

Could you not just format the cell into fractions, and use a custom format for it to always use 1/8ths. I do that for adding miles and chains.

[magpie patrick]

 

Decimal points and furlongs don't really fit together to well.

 

 

 

This

 

If you are going to use furlongs, you are already down to the nearest 220 yards, or ten boat lengths

 

Bradshaw only very occasionally breaks things down into less than 1 furlong

 

If you really must, work out how to do chains as well!

 

As suggested above a work around is to have three columns, in the first two you enter the miles and then the furlongs, and the third has a formula that either multiplies the miles by 8 or divides the furlongs by 8. Use the third column for addition and then reverse the process.

 

You can actually get Excel to work in base 8 but whether this can be applied to a single column I'm not sure

[Scholar Gypsy]

 

This

 

If you are going to use furlongs, you are already down to the nearest 220 yards, or ten boat lengths

 

Bradshaw only very occasionally breaks things down into less than 1 furlong

 

If you really must, work out how to do chains as well!

 

As suggested above a work around is to have three columns, in the first two you enter the miles and then the furlongs, and the third has a formula that either multiplies the miles by 8 or divides the furlongs by 8. Use the third column for addition and then reverse the process.

 

You can actually get Excel to work in base 8 but whether this can be applied to a single column I'm not sure

 

My post above shows how to do this without creating a third column.

 

I have a bit of a dislike of using some of the clever formatting in Excel. For example one can get it to display data stored as £000 so it looks like you are counting in £m. That's a good way to make a big mistake.

[magpie patrick]

 

My post above shows how to do this without creating a third column.

 

I have a bit of a dislike of using some of the clever formatting in Excel. For example one can get it to display data stored as £000 so it looks like you are counting in £m. That's a good way to make a big mistake.

 

Apologies - yes, your suggested method isn't the same thing as my work around, and doesn't need extra columns.

 

One does indeed have to be very careful with spreadsheets (although hopefully miles and furlongs on a trip isn't business critical!) - I find it essential to have a rough idea what the answer should be, just so you know the outcome makes sense and can check why not if it doesn't

[serenility]

Canalplan will do it for you in the journey planner. in preferences just tick 'show cumulative data'

[twbm]

Only on the IOW ....

 

Next week: 'How do I convert litres into bushels?'

 

 

 

 

 

 

(36.4 in the UK).

[tidal]

There are about 4 ways I can see of doing this.
Magpie's works as well as any other but precludes absolute accuracy (but then so does the recording method suggested by the op)

If you really want to get flash you can separate it down to feet (or lower) primarily and upscale from there into whichever format you like.

But tbh that isn't going to work unless you use either a continuously recording satellite tracking system or a very expensive and troublesome log system.

 

Therefore MP's solution is simplest and it can be extended beyond furlongs using the same methods by simply adding further columns with different conversion factors.Do remember that calculation columns can, once proven to work accurately, be hidden to simplify the final display.

 

Why the hell you would want to go down that route escapes me as a) that level of accuracy is hard to achieve without specialist equipment, b)AT BEST you are probably only measuring linear distance along the canal bank (as opposed to distance travelled) and c) What is the use of recording in such archaic formats ?

 

It's not as if you have any significant navigational hazards to avoid or get very far from the nearest shore

[Machpoint005]

It is pointless quoting distances to a greater apparent precision than the uncertainty inherent in the calculation.

 

On the basis that it is probably time, not mileage (or furlongage) that matters, I see no need to reckon distances any more "accurately" than the nearest half-mile, especially if there are locks or movable structures along the way.

 

Why measure speed in furlongs per fortnight?

[Tacet]

If it seems wrong to show those furlongs in a modern arabic style, then Excel has a function to convert to Roman numerals.

 

=ROMAN(A1)

 

Hope that helps.

[Victor Vectis]

Thanks for all the replies, yes even the 'helpful' ones!

 

Scholar Gypsy's file does the trick and is exactly what I was looking for.

 

I know it's daft but I like to calculate 'Red Wharf's' progress in miles and furlongs.

 

Thanks again.

 

Now, how do you convert furlongs per fortnight into rods, poles or perches per Bank Holiday Monday?

Edited October 2, 2015 by Victor Vectis

[tidal]

Thanks for all the replies, yes even the 'helpful' ones!

 

Scholar Gypsy's file does the trick and is exactly what I was looking for.

 

I know it's daft but I like to calculate 'Red Wharf's' progress in miles and furlongs.

 

Thanks again.

 

Now, how do you convert furlongs per fortnight into rods, poles or perches per Bank Holiday Monday?

 

There are 10 chains per furlong (Gunters) and 4 rods per chain and after that you are on your own because I never travel on a Bank Holiday Monday

[Peter X]

If it seems wrong to show those furlongs in a modern arabic style, then Excel has a function to convert to Roman numerals.

 

=ROMAN(A1)

 

Hope that helps.

Excellent! This is a great application of modern IT to an antiquated purpose, up there with when I bought a 100 year old scythe for a tenner on eBay to mow a sea of nettles and other weeds after renting a house with a large garden. Some years later it broke and I went back to eBay and bought another. The second one was the same price but came with a pick mattock which I've never used.

 

I must find a place to sneak some Roman numerals into my use of Excel at work.

[Victor Vectis]

If it seems wrong to show those furlongs in a modern arabic style, then Excel has a function to convert to Roman numerals.

 

=ROMAN(A1)

 

Hope that helps.

Wow! I've just tried this, (just for fun and nowt to do with the OP)

 

I never cease to be amazed by what Excell can do.

[TheBiscuits]

 

I never cease to be amazed by what Excell can do.

That's because you are a closet PHB and think Excel is a development platform.

 

You need Dave Mayall to let you know what you should be using, then you will only have the budget for Libre Office!

[Scholar Gypsy]

My next problem is to convert lat and longitude (measured in degrees and then minutes, with 6 decimal places) into an OS grid reference. There are some quite complex spreadsheets that do this...

[TheBiscuits]

My next problem is to convert lat and longitude (measured in degrees and then minutes, with 6 decimal places) into an OS grid reference. There are some quite complex spreadsheets that do this...

Lmgtfy.

 

http://www.movable-type.co.uk/scripts/latlong-gridref.html

 

Fat finger double post edit.

Edited October 2, 2015 by TheBiscuits

[Scholar Gypsy]

Thanks - I think I might have found that site before.

 

I would need to do some coding to automate the process, i.e to convert a load of L&L to grid references and write the results somewhere. That looks doable in Excel & Visual Basic. It is not a priority task though.

[David Mack]

Thanks - I think I might have found that site before.

 

I would need to do some coding to automate the process, i.e to convert a load of L&L to grid references and write the results somewhere. That looks doable in Excel & Visual Basic. It is not a priority task though.

A couple of years ago I wrote a spreadsheet to (amongst other things) convert thousands of L&L values to the Toronto map grid. I'll see if I can dig out the relevant bits.

[Woodsy]

My next problem is to convert lat and longitude (measured in degrees and then minutes, with 6 decimal places) into an OS grid reference. There are some quite complex spreadsheets that do this...

Lat long Calc Pro a app for android (and Apple I think) devices by Cruthu services will do this if you have a smart phone or tablet