Running Pace Chart

[greerso]

Hi Guy's,

I've been searching the world wide web to try and find an excel formula which will convert pace per mile to pace per kilometre. So if I enter a value in one of the cells it will covert to the opposite and visa versa.

Thanks in advance!

Mark

[AliGW]

Have a look at the CONVERT function: https://support.office.com/en-us/art...rs=en-US&ad=US

[BadlySpelledBuoy]

Perhaps the attached will help?
Total run time goes in column B, total distance in C, there's a drop down to chose between M or KM in D and the pace per mile and pace per KM will calculate in E and F respectively.

I've set it up as a tracker as it's similar to something I use, but the calculations are in there if all you want to do is build a matrix to convert between M & KM.

BSB

[greerso]

Ali thanks for your info it will help me out with something I need to do. BSB thanks for an excellent tool. I'll definitely be able to use it from time to time. What I was looking was probably simpler. If I put the km pace in 1 cell can I convert it to mile pace in the opposite cell. If you get my meaning.

Kind Regards Mark

[AliGW]

Can you provide a specific example?

[BadlySpelledBuoy]

If A1 = the pace you wish to convert and B1 = either M or KM depending on which you're converting from, then the below in C1 should do what you need.

Please Login or Register  to view this content.

See Sheet2 in the attached.

BSB

[greerso]

Gosh Ali, I hope this helps. I'm a multi sports coach and the way I have things set up at the minute is by way of a very long list of mile paces and their equivalent km pace. When I open up my workbook all the data I need for a particular person is contained on it. All I wanted to be able to do is enter a pace in a cell say 4:30 which is a minute mile pace and in the adjacent cell it gave me the KM pace. Or if I added the 2:48 in it's respective cell it changed to $:30 in it's respective cell. I'm looking to do something similar for heights and weights.

4:30 = 2:48
4:35 = 2:51
4:40 = 2:54
4:45 = 2:57
4:50= 3:00
4:55 = 3:03
5:00 = 3:06
5:05 = 3:09
5:10 = 3:13

Thanks Mark

[BadlySpelledBuoy]

You wish to have it so you can enter a number in either one cell (for M) or another cell (for KM) and have it calculate in the opposite cell then you'll need to use VBA to perform the calculation and update the results.

BSB

[greerso]

Thanks BSB that's excellent - whats VBM?

[BadlySpelledBuoy]

VBA is Visual Basic for Applications. It's a programming language that you can use in Excel (and other programs) to do lots of fancy stuff.
In this case you'd need to use it as for the calculation to work 'normally' you'd need the formula in both the KM and the M cells. But you want to be able to enter a value in either of these cells which would overwrite the formula. So you'd need the calculation to happen in memory when a specific event happens and then the result show in the relevant cell.

I'll put something together for you.

BSB

[BadlySpelledBuoy]

Try this. The code I've used (below) is stored in the sheet module. If you right click the sheet tab and select "view code" you'll find it.

Please Login or Register  to view this content.

BSB

[greerso]

Thanks BSB, Yours doesn't appear to working unless it's me is the problem. Where and how do I enter the formula on my existing worksheet. If it's too much trouble don't be worrying about it, I thought it was much simpler.

[BadlySpelledBuoy]

What do you mean by mine doesn't appear to be working?

In the attachment in post #11 everything is set up. If you enter a pace in M in cell A2 then cell B2 will show the equivalent in KM and if you enter the pace in KM in cell B2 then A2 will show the equivalent in M.

If you want to translate that to your real workbook then copy the code in post #11, right click the tab for the relevant sheet in your workbook and select "View Code". Paste the code in there and adjust the cell references to reflect the cells you're actually using and that should be it done.

If you get stuck, feel free to attach your workbook and I'll amend the code for you.

BSB