ajd1992
Topic Author
Posts: 2390
Joined: Mon Jul 24, 2006 7:11 am

Excel Boffins: Help With Formulae?

Thu Apr 15, 2010 6:49 pm

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.

Thanks in advance, guys.
 
User avatar
casinterest
Posts: 5358
Joined: Sat Feb 12, 2005 5:30 am

RE: Excel Boffins: Help With Formulae?

Thu Apr 15, 2010 7:47 pm

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
Posts: 3400
Joined: Thu Aug 07, 2003 11:54 am

RE: Excel Boffins: Help With Formulae?

Thu Apr 15, 2010 11:10 pm

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)
Engineers Rule The World!!!!!
 
User avatar
Aaron747
Posts: 8529
Joined: Thu Aug 07, 2003 2:07 am

RE: Excel Boffins: Help With Formulae?

Thu Apr 15, 2010 11:25 pm

Quoting ajd1992 (Thread starter):
I hate excel, I had to use it at school and I've forgotten everything I used to know unfortunately

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.
If you need someone to blame / throw a rock in the air / you'll hit someone guilty
 
ajd1992
Topic Author
Posts: 2390
Joined: Mon Jul 24, 2006 7:11 am

RE: Excel Boffins: Help With Formulae?

Fri Apr 16, 2010 1:41 am

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
Posts: 3453
Joined: Tue Aug 10, 2004 5:31 am

RE: Excel Boffins: Help With Formulae?

Fri Apr 16, 2010 1:47 am

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, your recycle old airplanes!

Who is online

Users browsing this forum: fr8mech and 7 guests