Forum Discussion
Average Elapsed Time
You may simplify a bit using formulae as
Average returns correct results, please open attached. Perhaps you may share sample file?
=AVERAGE(J16:J18) produces 13:45 on my computer.
The average of the hours is 5:45.
You may simplify a bit using formulae as
Average returns correct results, please open attached. Perhaps you may share sample file?
- Thanks so much Sergei. Someone at the Excel Help Forum gave me a similar answer. Your solution is very good. I have not seen the MOD function used with the "-1". How does that work?
Let's take E18 = 23:00 and I18 = 09:00 as example.
I18-E18 = 9:00 - 23:00 = -14:00
Excel stores times internally as numbers, with 1 day = 24 hours as unit, so for example 6:00 is stored as 0.25 and 12:00 is stored as 0.5.
The MOD function calculates the remainder after division.
For example MOD(17,5) returns 2 because 17 goes 3 times into 5, leaving 2.
And MOD(-14,24) returns 10 because -14 goes -1 times into 24, leaving 10 (i.e. -14 = -1*24+10).
Similarly, MOD(-14:00, 1) returns 10:00 since 1 is equivalent to 24 hours.
- Thanks Hans.
