This guide will explain how to calculate the average time in Excel.
Since time is stored as a number in Excel, we can easily find the average time given a range of time values.
Users can quickly work with time data in Excel. For example, we can quickly compare two different time values to see which time comes earliest. We can also find the difference between two given time values to know how much time elapsed between these two times.
We can also find the average time given a set of time values. Let’s take a look at a quick example!
Suppose you are keeping track of the times employees clock in for work every day. You want to determine the average time your employees arrive for work.
Since Excel stores dates and times as serial numbers, we can use the AVERAGE function to find the mean arrival time of your employees.
We can also use similar functions to find different averages. For example, we can use the AVERAGEIF function to only average time values that meet certain criteria.
Now that we have an idea of when to calculate the average time in Excel, let’s learn how to perform this calculation on an actual sample spreadsheet.
A Real Example of Calculating the Average Time in Excel
The following section provides several examples of how to use Excel functions to calculate the average time from a range of time values. We will also explain the formulas and tools used in these examples.
First, let’s take a look at our sample data. We have a dataset of recorded arrival times of various guests for an event that starts at 12PM.
We first want to determine the average arrival time of all guests.
To get the average arrival time in cell D2, we just need to use the following formula:
=AVERAGE(A2:A12)
The AVERAGE function finds the total of all values in a range and divides the total by the number of provided values.
Next, you want to compute the average arrival times for late guests. This means that we should only count guests who arrived past 12PM.
We can find the average arrival time of late guests using the following formula:
=AVERAGEIF(A2:A12,”>12:00:00 PM”, A2:A12)
Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below.
If you’re ready to try calculating the average time, follow the steps in the next section!
How to Calculate the Average Time in Excel
This section will guide you through each step needed to start computing the average time in Excel.
You’ll learn how to use the AVERAGE function to find the average time given a range of time values. We will also show how to use the AVERAGEIF function to find the average time for values that follow certain criteria.
Follow these steps to start calculating the average time in Excel.:
- First, select the range that includes all the time values you want to average.
In this example, we want to find the average arrival time from a list of values in the range A2:A12. - In the Home tab, click on the dropdown menu under the Number section to reveal options for number formatting. Click on the Time option to apply the same time formatting across the selected range.
- The selected range should now have the same number formatting applied.
In our example above, we’ve added an AM/PM indicator to the displayed text. - Next, select the cell you want to output the average time.
In this example, we’ve chosen cell D2 to hold our average arrival time. - Since time values are stored as numbers in Excel, we can use the
AVERAGEfunction to find the average time. Enter the range with your time values as the argument of theAVERAGEfunction.
Hit the Enter key to return the average time. In this example, we see that the average arrival time of all guests is 12:09:55 PM.
- If you want to exclude certain values from the averaging computation, you can try using the
AVERAGEIFfunction instead.
In the example above, we’ve excluded all time values that are earlier than 12:00:00PM.
These are all the steps needed to calculate the average time in Excel.
This step-by-step guide should provide you with all the information you need to begin calculating the average time in Excel.
We’ve shown you how to use the AVERAGE and AVERAGEIF function to compute for the average time in a range of time values.
The AVERAGE and AVERAGEIF functions are just a few of the many Excel functions you can use in your spreadsheets. Our website offers hundreds of other functions and methods to help you get more out of Microsoft Excel.
For example, you can read our guide on how to convert time values into decimals in Excel to learn more ways to present your time data. With so many other Excel functions available, you can find one appropriate for your use case.
Don’t miss out on our team’s new spreadsheet tips, tricks, and best practices. Subscribe to our newsletter to stay updated on the latest guides from us!
