Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I'm keeping cycling diary in a Google spreadsheet.

Each entry has a value of Duration entered as ie. . Then I have the totals where duration is summarized as well. But whenever

Some of the relevant columns:

  • Distance
  • Duration (entered as 3:45:30 meaning 3 hours, 45 minutes and 30 seconds)
  • Average speed - calculated based on the following formula:

    =if(or(isblank(C3);isblank(D3));;C3/(HOUR(D3)+MINUTE(D3)/60+second(D3)/3600))
    

This correctly calculates my average speed.

But. Then I also calculate totals at the top of each column. Distance and time are summed up, but speed is calculated on these two sums because I can't simply make an average of calculated speeds since each of them depends on the course length.

But. Whenever total time exceeds 24 hours, formula for getting hours out of some value returns value between 0 and 23.

So if you run ie. =hour("25:30:20") it will return 1 but I want this (or any other that I'm not aware of) function to return 25. Of course my speed calculation is completely off.

How should I get all hours from my time summary?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

Solution

Suppose total distance is in column C1 and total time is in D1 then average speed formula would be:

=C1/(int(D1)*24+hour(D1)+minute(D1)/60+second(D1)/3600)

int function is the one I was after.

share|improve this answer

A more simple version is =D1/(T1*24) where D1 is distance and T1 is total time.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.