Average Elapsed Time

[WayneS1947]

Excel 2013.  All cells in this spreadsheet are formatted as Time. 

Column I is the End Time. Column E is the Start Time. 

Column F includes minutes to deduct from the Elapsed time.

Column H includes minutes to deduct from the Elapsed time.

Column G contains no data.

Column J contains cells with this formula to calculate the Elapsed Time:

=IF(I16=E16,I16-E16-F16-H16,I16-F16-H16+1-E16)

=IF(I17=E17,I17-E17-F17-H17,I17-F17-H17+1-E17)

=IF(I18=E18,I18-E18-F18-H18,I18-F18-H18+1-E18)

How can I calculate the average of the elapsed times in Column J?

[#2]

@WayneS1947 

=AVERAGE(J16:J18)

Adjust the range J16:J18; it does not matter if it includes empty cells.

[#3]

Thanks Hans.

=AVERAGE(J16:J18) produces 13:45 on my computer.
The average of the hours is 5:45.

[#4 · Accepted solution]

@WayneS1947 

You may simplify a bit using formulae as

Average returns correct results, please open attached. Perhaps you may share sample file?

[#5]

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?

[#6]

@WayneS1947 

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.

[#7]

Thanks Hans.