How can I calculate the average time in Google Sheets?

By /

To calculate the average time in Google Sheets, you can use the AVERAGE function. This function takes the values of a selected range and calculates the average for that range. However, since time is in the form of numbers in Google Sheets, you will need to format the cells as time in order for the function to work properly. Once the cells are formatted, you can select the range of cells that contain the times and use the AVERAGE function to calculate the average time. The result will be displayed in the format of “hours:minutes:seconds”.

You can use the following formula to calculate an average time value in Google Sheets:

=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 Google Sheets

Suppose we have the following list of times in Google Sheets:

To verify that the times are in a valid format, we can highlight the range A2:A11, then click the Format tab along the top menu, then click Number, then check to see the number format being used:

We can see that Google Sheets has correctly identified the values in the range A2:A11 as Time.

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:

Google Sheets calculate average time

We can see that the average time is 12:11:51 PM.

Example: Calculate Average Time with Condition in Google Sheets

We can also use an AVERAGEIF function to calculate the average time based on a condition in Google Sheets.

=AVERAGEIF(A2:A11, ">12:00:00 PM")

The following screenshot shows how to use this formula in practice:

Google Sheets returns a numerical value but we can click the Format tab again, then click the Number, then 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.

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

x

Related terms:

  1. Google Sheets: Average Across Multiple Sheets
  2. How can I calculate the average of a group of numbers between two dates in Google Sheets?
  3. How to calculate average excluding outliers in google sheets?
  4. How do I calculate a weighted average in Google Sheets?
  5. Google Sheets: How to Calculate Average If Cell Contains Text
  6. How to Calculate Average If Not Blank in Google Sheets
  7. How to Calculate Exponential Moving Average in Google Sheets?
  8. How do you calculate the average difference in Google Sheets?
  9. How can I calculate the average in Google Sheets and round the result?
  10. Count Specific Characters in Google Sheets How to count specific characters in google sheets?