Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
I record my rides on an Excel spreadsheet. On Saturday I rode 29.4 miles in 4 hours 18 mins. How can I set a formula to convert this to mph? If I enter the time as 4.18 Excel regards the minutes (18) as a fraction, but if I use the time formula 4:18 then the calculation comes up with silly answer.
Does anyone know how to set a formula to do the calculation?
convert time to a decimal, 4.18 treat as 4 and 18/60. either enter over two cells, so you have an hour cell and a minutes cell or you need to break down the number so it is treated as such.
you can then add 4 and 18/60 to give the timetaken.
Thanks, but that's too complicated and needs too many cells/key strokes. I've managed to find a formula that I can programme into my spreadsheet that will carry out the calculation for me and all I have to do is enter the values into pre formatted cells. A little bit of work now then easy peasy ever after.
=QUOTIENT(B1,1)+(MOD(B1, 1)/0.6)/A1
Typed into C1 where A1 is miles and B1 is time in the form H.MM
I was sent :-
Format cell B1 as Time, B2 as Number and B3 as Number. Enter 4:18 into B1 and 29.2 into B2 and =B2/((B1-INT(B1))*24) into B3
Needs a bit of tweaking to get it working across the page.
Enter the miles in cell A1 formatted as number
Enter time in cell B1 in TIME format (i.e. 4:18). The time format stores the number as a decimal of a day, e.g. 12:00 is stored as 0.5, 12 hours = 0.5 days)
So enter formula in cell C1 formatted as a number =(A1/B1)/24.
Ta da.