In Excel, when we need to calculate the average days of different dates depending on our data set, you may find it somewhat difficult. This article will show you how to calculate average days in Excel with two convenient methods.
How to Calculate Average Days in Excel: 2 Easy Approaches
In this article, we will demonstrate to you how to calculate average days in Excel by utilizing the AVERAGE function and the combination of the SUMPRODUCT function and the COUNT function. Let’s suppose we have a sample data set.
1. Utilizing Average Function to Calculate Average Days in Excel
In this section, we will determine the differences between the two dates in the given data set and then we will apply the AVERAGE function to calculate the average days in Excel.
- Firstly, select the D5 cell.
- Then write down the following formula in the D5 cell.
- After that, press ENTER.
- Here, you will see the first difference in the D5 cell between the first end date and the start date.
- Then, use the Fill Handle tool and drag it down from the D5 cell to the D14 cell.
- Finally, you will find all the differences between the end date and the start date.
- In this part, apply the following average formula in the D16 cell.
- After that, hit ENTER.
- As a result, you will observe the average days in the D16 cell in the below image.
2. Combining SUMPRODUCT and COUNT Functions to Calculate Average Days
In this second section, we will apply the SUMPRODUCT function and the COUNT function at a time to evaluate the average days in Excel. The sum of the products of related ranges or arrays is returned by the SUMPRODUCT function. Although addition, subtraction, and division are also options, multiplication is the default operator. The COUNT function in Excel provides a count of values that are numerical values. Negative numbers, percentages, dates, times, numbers, fractions, and formulas that return numbers are all examples of numbers. Text values and empty cells are discarded.
- Firstly, select the D16 cell.
- Then, type the following formula here to determine the average days in Excel.
- =SUMPRODUCT(C5:C14-B5:B14): This portion of the formula returns the value in terms of the end date and start date by applying the SUMPRODUCT subtraction formula and the value is 104.
- =SUMPRODUCT(C5:C14-B5:B14)/COUNT(C5:C14): This is a combination of the SUMPRODUCT function and the COUNT function which returns the final result of average days 10.4.
- After that, press ENTER.
- Consequently, you will see the following result of the average days in Excel.
Read More: How to Calculate Average of Text in Excel
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it by yourself.
In this article, we’ve covered 2 handy methods to calculate average days in Excel. We sincerely hope you enjoyed and learned a lot from this article. Additionally, If you have any questions, comments, or recommendations, kindly leave them in the comment section below.
- How to Calculate Average Rating in Excel
- How to Calculate 5 Star Rating Average in Excel
- How to Calculate Average Growth Rate in Excel
- How to Get Average Time in Excel
- How to Calculate Daily Average in Excel
- How to Calculate Weekly Average in Excel
- How to Calculate Monthly Average from Daily Data in Excel