So basically, I have a sheet where speeds in kilometers per hour (km/h) are written in cells. I sometimes convert these speeds into paces (in minutes per kilometer (mn/km)). To do so, I apply the integer function to an inverse function used on the speed divided by 60 -> INT(1/(speedCell/60)). This gives me the minutes part of the pace. For example, let's admit that I have a speed of 13 km/h; the pace of that speed is 4'36/km. The formula gives me the minutes : =INT(1/(13/60)) -> 4.

Now I need to get the seconds part. I do it using the truncate function over the subtraction of the speed converted directly into a pace and the integer part of the pace. Formula applied : =TRUNC((1/(13/60)-INT(1/(13/60)))*60) -> 36. Simplified, this is : =PACE(minutes and seconds)-PACE(minutes) -> seconds.

Here comes the problem. I format these two formulas into : =FORMULA1&"'"&FORMULA2"/km". My question is : How can I use the result, now that it is being formatted with a string in the middle of the formula?

I used to use the format styling to add a unit behind the value; but since there is an apostrophe in the middle of the formula, I don't know how to do it.

Thank you for your help, don't hesitate to ask, if you have questions over the understanding of the problem.

CodePudding user response：

I'm not sure if I understood correctly. But instead of concatenating the pace parts "manually", I recommend building a time value from the minutes / seconds values using the TIME(hours ; minutes ; seconds) function. Now, you can use the cell formatting templates to generate an output like [min]'[sec]/km with the following format code

MM'SS"/km"

and do calculations.

BTW: you could also use the MOD() function to get the seconds part:

=INT(MOD((1/(13/60));1)*60)