SOLVED

# Average Elapsed Time

Copper Contributor

# Average Elapsed Time

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?

6 Replies

# Re: Average Elapsed Time

=AVERAGE(J16:J18)

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

# Re: Average Elapsed Time

Thanks Hans.

=AVERAGE(J16:J18) produces 13:45 on my computer.
The average of the hours is 5:45.
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Average Elapsed Time

You may simplify a bit using formulae as

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

# Re: Average Elapsed Time

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?

# Re: Average Elapsed Time

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.

# Re: Average Elapsed Time

Thanks Hans.
1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Average Elapsed Time

You may simplify a bit using formulae as

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