Table of Contents
Average time in Excel can be calculated by using the AVERAGE and TIME functions. The AVERAGE function can take a range of values, such as a list of times, and calculate the arithmetic mean. The TIME function can be used to convert a number of hours, minutes, and seconds to a time-formatted value. Once these two functions are combined, the average time of a range of values can be calculated. Examples of how to use these functions in an Excel worksheet are provided.
You can use the following formula to calculate an average time value in Excel:
=AVERAGE(A2:A11)
This particular formula calculates the average time value in the range A2:A11 and assumes that each value in the range is in a valid time format.
The following example shows how to use this formula in practice.
Example: Calculate Average Time in Excel
Suppose we have the following list of times in Excel:
To verify that the times are in a valid format, we can highlight the range A2:A11, then click the Number Format dropdown menu from the Number tab within the Home tab on the top ribbon:
Once we click More Number Formats, a new screen will appear that shows which format Excel has chosen for the values:
In this example, Excel has chosen h:mm:ss AM/PM, which is the correct time format.
If your time values are in another format, simply click on the appropriate format from the list of options.
We can then click OK.
Next, we can use the following formula to calculate the average time value:
=AVERAGE(A2:A11)
The following screenshot shows how to use this formula in practice:
We can see that the average time is 12:11:51 PM.
Example: Calculate Average Time with Condition in Excel
We can also use an AVERAGEIF function to calculate the average time based on a condition in Excel.
For example, we can use the following formula to calculate the average time only for the times that occur after 12 PM:
=AVERAGEIF(A2:A11, ">12:00:00 PM")
The following screenshot shows how to use this formula in practice:
Excel returns a numerical value, but we can click the Number Format dropdown menu again and click Time to convert this numeric value to a time value:
The average time among the times that occur after 12 PM is 5:18:19 PM.