ajd1992 From , joined Dec 1969, posts, RR: Posted (6 years 1 month 1 week 5 days 16 hours ago) and read 2882 times:

Evening,

I want to set out a spreadsheet to keep track exercise mileage for bike riding, but I'm not sure how I'd go about it. My bike computer measures in metres, which isn't an issue. I've looked online for a formula that would allow me to convert that to miles/kms in separate columns (I know that'd be 2 different formulae) but it's got me stumped (I hate excel, I had to use it at school and I've forgotten everything I used to know unfortunately). I'd like the average MPH as well but that's not a major issue to work out by hand, I'd just rather let a computer do that

I know how to set it out basically (it'll be sorted by "Week Starting" then I'll have my week total at the bottom of each week, that part is easy), but the convert one has got me stuck.

casinterest From United States of America, joined Feb 2005, 5819 posts, RR: 3
Reply 1, posted (6 years 1 month 1 week 5 days 15 hours ago) and read 2832 times:

Something like this

A B C D E
Date Time (Hours) Time (minutes) Meters KM Miles MPH
7/6/2009 1 30 21000 21 13.04879504 8.699196691
7/12/2009 2 40 39000 39 24.2334765 9.087553686
1/12/2010 3 0 100,000 100 62.13711922 20.71237307

Convert Meters to Kilometers =+CONVERT($D5,"m","km")
Convert Meters to Miles =+CONVERT($D5,"m","mi")

MPH =CONVERT(D5,"m","mi")/($B5+$C5/60)

Much prettier in the actual spreadsheet.

IM me with your email , if you want the template.

[Edited 2010-04-15 12:48:15]

Older than I just was ,and younger than I will soo be.

StarAC17 From Canada, joined Aug 2003, 3754 posts, RR: 9
Reply 2, posted (6 years 1 month 1 week 5 days 12 hours ago) and read 2759 times:

Quoting ajd1992 (Thread starter): I want to set out a spreadsheet to keep track exercise mileage for bike riding, but I'm not sure how I'd go about it. My bike computer measures in metres, which isn't an issue. I've looked online for a formula that would allow me to convert that to miles/kms in separate columns (I know that'd be 2 different formulae) but it's got me stumped (I hate excel, I had to use it at school and I've forgotten everything I used to know unfortunately). I'd like the average MPH as well but that's not a major issue to work out by hand, I'd just rather let a computer do that

In case you don't know the conversion factors off the top of your head, these might make the formulas a bit easier because its just a simple multiplication/division of one cell to the factor you need.

These are the only one I think you will need

1 km = 1000 meters
1 km = 0.621 miles (approximately)
1 mile = 1610.3 meters (approximately)

ajd1992 From , joined Dec 1969, posts, RR:
Reply 4, posted (6 years 1 month 1 week 5 days 10 hours ago) and read 2707 times:

Quoting StarAC17 (Reply 2):
In case you don't know the conversion factors off the top of your head, these might make the formulas a bit easier because its just a simple multiplication/division of one cell to the factor you need.

These are the only one I think you will need

1 km = 1000 meters
1 km = 0.621 miles (approximately)
1 mile = 1610.3 meters (approximately)

I know all the conversions, I was brought up using both, I just don't know the formulas. I haven't used excel in nearly 3 years to be fair.

Quoting Aaron747 (Reply 3): You may hate it but really need to know it well. Just about any job worth anything makes use of it some time or another.

Yeah I know but I don't use it, so I've fallen out of practice.

JAGflyer From Canada, joined Aug 2004, 3754 posts, RR: 4
Reply 5, posted (6 years 1 month 1 week 5 days 9 hours ago) and read 2706 times:

If you're data is in cell C5 and its 5000 meters which you want to convert to KM in cell D5 it is as follows:

=(C5/1000)

The answer in D5 will be "5"

Drag the formula down by clicking and holding the black thing in the bottom of the first cell if you want to convert all the distances in the "C" column to meters.

Support the beer and soda can industry, recycle old airplanes!