distance pace calculation

[rossnixon]

I run a certain distance in a certain time.
I have got some formulas that give the correct result, but need some formatting.

e.g. I run 5.02km in 00:26:06. Formula gives me pace of 5:12 which is correct.
e.g. I run 5.02km in 00:25:52. Formula gives me pace of 5:9 which should be formatted as 5:09

I have a preliminary formula to get me part way there.

Final formula is =INT(G3)&":"&ROUND((G3-INT(G3))*60)

=INT(G3) gives me the minute portion of the answer.

ROUND((G3-INT(G3))*60) gives me the seconds portion of the answer.

How could I convert a number such as 9 into a string that reads 09 ?
Can it be done within my final formula, or do I need another column to sort it out?

---

Tackling it from another angle, I now have the correct result as a number.

=INT(G3)+(ROUND(MOD(G3,SIGN(G3))*60))/100 gives me the result 5.09

Is there a way to choose : as the decimal point character? So I get 5:09 instead?

[Ratslinger]

Hello,

Why not cells formatted as HH:MM:SS then G3 = time & other cell is =G3/5.02

Gives:

The result cell could even be formatted with MM:SS.00 to result as 05:09:16

[rossnixon]

Oh, thanks Ratslinger. Just saw your answer after I posted mine.
Will have a play with formatting your solution.
Ah, got it: format answer as M:SS (assumes I’m never slower than 10 mins/km).

Hmm. One more thing to think about:

I want a result like 5:11.9 to be rounded to 5:12.
At the moment it shows 5:11

This seems to work. I add 00:00.50 from a special cell.

[keme]

… assumes I’m never slower than 10 mins/km

Not at all! That single M does not limit display to single digit, but it forces at least one digit. That means you can use the same setup for swimming/walk/slow jog exercise too, no problem.

---

If you are crawling along the path, or swim at moderate pace, a km may take more than 60 minutes, which then wraps to the hour which is not included in your format. Brackets around a time format segment will accumulate that beyond the “wrap point”. In other words, you use format string [M]:SS to correctly display a pace >60 minutes per km. You could also use H:MM:SS, but I guess you normally would not want that display…

[rossnixon]

Looks like I’ve got a solution, but very limited testing so far.

=TEXT(INT(G3),"#")&":"&TEXT((ROUND(MOD(G3,SIGN(G3))*60)),“0#”)

[PKG]

Why so complicated

Runtime.ods