Nazmus Sakib
Manually calculating the average between two given dates in a large dataset is inefficient and cumbersome. We can easily enter a simple formula to find the average within a date range. In this article, we will describe how to average if within date range in Excel with 3 convenient approaches.
📁 Download Excel File
Download the following Excel workbook to realize the topic more clearly.
Learn to Calculate Average If Date Within Range in Excel with These 3 Suitable Approaches
Here, we will use the following sample dataset to calculate average within a date range in Excel.
1. Using AVERAGEIFS Function
In our first approach, we will use the AVERAGEIFS function to determine the average within a date range.
⬇️⬇️ STEPS ⬇️⬇️
- In the beginning, type in the Start and End Date in G5 and G6 cells respectively.
- In our case, we used 12 December 2022 as the Start Date and 18 December 2022 as the End Date. We will use these two dates as our date range.
- Next, select cell G7 and Enter the formula below:
=AVERAGEIFS(D6:D14,B6:B14,”>=”&G5,B6:B14,”<=”&G6)
- Consequently, we have calculated the Average Profit from 12 December 2022 to 18 December 2022.
Read More: 9 Easy Ways to Calculate Date Range in Excel
2. Combining AVERAGE and IF Functions
Afterward, we will merge AVERAGE and IF functions to average if within date range.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, we will enter the Start and End Dates in cells G5 and G6.
- Later, type in the following formula in cell G7 and press Enter:
=AVERAGE(IF((B6:B14>=G5)*(B6:B14<=G6),D6:D14))
- Voila! Eventually, our Average Profit from dates 12 December to 18 December 2022 is in cell G7.
🔨 Formula Breakdown
AVERAGE(IF((B6:B14>=G5)*(B6:B14<=G6),D6:D14))
👉 The IF function will find dates greater than the date stored in cell G5 and less than the date stored in cell G6 from cells B6:B14.
👉 Then, it will return the average of the corresponding Profit from cells D6:D14 with the help of the AVERAGE function.
Note:
- For older Excel versions e.g. 2019, press Ctrl+Shift+Enter to apply the formula in cell G7.
- After that, you will notice a pair of curly brackets around the formula which depicts the formula as an array formula.
3. Applying SUMPRODUCT Function
In our final approach, we will use the SUMPRODUCT function to average if within a date range.
⬇️⬇️ STEPS ⬇️⬇️
- To begin with, Enter the Start and End Dates in cells G5 and G6 as the date range.
- Afterward, in cell G7, Enter the formula below:
=SUMPRODUCT(((B6:B14>=G5)+0),((B6:B14<=G6)+0),D6:D14)/SUMPRODUCT(((B6:B14>=G5)+0),((B6:B14<=G6)+0))
- Done! Momentarily, we will get the Average Profit in cell G7.
🔨 Formula Breakdown
SUMPRODUCT(((B6:B14>=G5)+0),((B6:B14<=G6)+0),D6:D14)/SUMPRODUCT(((B6:B14>=G5)+0),((B6:B14<=G6)+0))
👉 (B6:B14>=G5) and (B6:B14<=G6) will return two arrays of TRUE and FALSE. They will return TRUE if the dates from cells B6:B14 are greater than the date stored in cell G5 and less than the date stored in cell G6 respectively. +0 is added to convert the boolean arrays to binary arrays of 1s and 0s.
👉 Next, the SUMPRODUCT function will sum the Profit from cells D6:D14 where the above conditions are satisfied.
👉 The second SUMPRODUCT function will return the number of cells where the conditions are fulfilled.
👉 Thus, we will get the Average Profit.
Read More: 7 Tricks to Apply SUMIFS in Date Range with Multiple Criteria
📝 Takeaways from This Article
📌 Firstly, we learned how to find Average Profit within a date range using the AVERAGEIFS function.
📌 Secondly, we demonstrated the combination of AVERAGE and IF functions to average if within date range.
📌 Lastly, we showed how to use the SUMPRODUCT function to average if within date range.
Conclusion
That concludes the discussion for today. These are some convenient methods to average if within date range in Excel. If you have any queries or recommendations, kindly notify us in the comments section. Downloading the practice sheet will help you comprehend the concepts better. Visit our website ExcelDen to find out about diverse kinds of Excel methods. We appreciate you taking the time to read this article.
