Estimating the average of a dataset is a common task in our regular professional life. But, sometimes, we need to find the average in a range. In this article, we will demonstrate 3 easy methods to find the average value if values lie between two numbers in Excel. If you are also curious about it, download our practice workbook and follow us.
How to Calculate Average If Values Lie Between Two Numbers in Excel: 3 Easy Methods
To demonstrate the approaches, we consider a dataset of examination marks of 10 students. The name of the students are in column B, and their marks are in column C. So, we can claim our dataset is in the range of cells B5:C14.
📚 Note:
All the operations of this article are accomplished by using the Microsoft Office 365 application.
1. Using AVERAGEIFS Function
In the first method, we will use the AVERAGEIFS function to get the average value if values lie between two numbers in Excel. We can apply the formula in two different ways. We are going to calculate the average of those values that lie between 85 to 95.
1.1 Directly Insert AVERAGEIFS Function
We can directly input the formula into our Excel worksheet to calculate the average of those values that lie between 85 to 95. The steps of this approach are given below:
📌 Steps:
- First of all, select any cell. To demonstrate the process, we select cell E5.
- Now, write down the following formula into the cell.
=AVERAGEIFS(C5:C14,C5:C14,">=85",C5:C14,"<=95")
- Then, press Enter.
- You will get the average value between the values between 85 and 95.
Thus, we can say that our formula works perfectly, and we are able to find the average if values lie between two numbers in Excel.
1.2 Insert AVERGAREIFS from AutoSum Command
We can also insert the formula using the AutoSum command to calculate the average of those values that lie between 85 to 95. The steps of this process are given as follows:
📌 Steps:
- At first, select any cell. To show the process, we select cell E5.
- Now, in the Home tab, select the drop-down arrow of the AutoSum > More Functions from the Editing group.
- As a result, a small dialog box called Insert Function will appear.
- Then, change the Or select a category option from Most Recently Used to All.
- After that, scroll down with your mouse and select the AVERAGEIFS function.
- Next, click OK.
- Another dialog box called Function Arguments will appear.
- Afterward, write down the following arguments in this dialog box.
- Finally, press OK.
- You will figure out the average value between the values between 85 and 95.
Hence, we can say that our procedure works effectively, and we are able to find the average if values lie between two numbers in Excel.
2. Utilizing AVERAGE and IF Functions
In this process, we are going to use the AVERAGE and IF functions to get the average value if values lie between two numbers in Excel. Here, we choose the upper bound at 95 and the lower bound at 85. The steps of this method are explained below:
📌 Steps:
- First, select any cell. To show the procedure, we select cell E5.
- After that, write down the following formula into the cell.
=AVERAGE(IF(C5:C14>=85,IF(C5:C14<=95,C5:C14)))
- Next, press Enter.
- You will get the average value between the values between 85 and 95.
Therefore, we can say that our formula works precisely, and we are able to find the average if values lie between two numbers in Excel.
🔎 Breakdown of the Formula
We are breaking down the formula for cell E5.
👉 IF(C5:C14<=95,C5:C14): The IF function checks whether the data is less than 95. If the logic is true the function will show the data. Otherwise, it will return FALSE. In this case, the function returns all the data.
👉 IF(C5:C14>=85,IF(C5:C14<=95,C5:C14)): The IF function checks whether the data is greater than 85. If the logic is true, the function will check for the second IF function. If this logic also satisfies, the function shows the dataset. Conversely, it will return FALSE. Here, the formula returns 85, 87, 89, and 93.
👉 AVERAGE(IF(C5:C14>=85,IF(C5:C14<=95,C5:C14))): The formula will show the average value of those four numbers. Here, the formula returns 88.5.
Read More: Excel AVERAGEIF with ‘Greater Than’ and ‘Less Than’ Criteria
3. Applying SUMIFS and COUNTIFS Functions
In this process, we will use the SUMIFS and COUNTIFS functions to get the average value if values lie between two numbers in Excel. We choose two dates September 3, 2022, and September 7, 2022, and evaluate the average value of the correspondent data that lie between them. In this method, we are going to use another dataset.
The steps of this process are described below:
📌 Steps:
- Firstly, select a cell. In this case, we select cell E10.
- Write down the following formula into the cell.
=SUMIFS(C5:C14,B5:B14,">="&$F$5,B5:B14,"<="&$F$6)/COUNTIFS(B5:B14,">="&$F$5,B5:B14,"<="&$F$6)
- Now, press Enter.
- You will get the average value of the working time between September 3, 2022, to September 7, 2022.
Finally, we can say that our formula works successfully, and we are able to find the average if values lie between two numbers in Excel.
🔎 Breakdown of the Formula
We are breaking down the formula for cell E10.
👉 SUMIFS(C5:C14,B5:B14,”>=”&$F$5,B5:B14,”<=”&$F$6): The SUMIFS function sum all the corresponded data which lies between September 3, 2022 and September 7, 2022. Here, the function returns 2380.
👉 COUNTIFS(B5:B14,”>=”&$F$5,B5:B14,”<=”&$F$6): The COUNTIFS function counts all the corresponded data which lies between September 3, 2022 and September 7, 2022. Here, the function returns 5.
👉 SUMIFS(C5:C14,B5:B14,”>=”&$F$5,B5:B14,”<=”&$F$6)/COUNTIFS(B5:B14,”>=”&$F$5,B5:B14, “<=”&$F$6): The formula will show the average value for the data which lies between the dates September 3, 2022 and September 7, 2022. Here, the formula returns 476.
Read More: How to Calculate Average If Number Matches Criteria in Excel
How to Calculate Average If Values Lie Between Two Dates in Excel
Here, we are going to calculate the average if the values lie between two dates. To estimate the average value the AVERAGEIFS function will help us. The procedure is explained below step-by-step:
📌 Steps:
- First of all, select any cell. We select cell E5.
- Afterward, write down the following formula into the cell.
=AVERAGEIFS(C5:C14,B5:B14,">=3/9/2022",B5:B14,"<=7/9/2022")
- Next, press Enter.
- You will get the average value between the dates September 3, 2022, to September 7, 2022.
So, we can say that our formula works perfectly, and we are able to find the average value if values lie between two dates in Excel.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
Conclusion
That’s the end of this article. I hope that this article will be helpful for you and you will be able to find the average if values lie between two numbers in Excel. Please share any further queries or recommendations with us in the comments section below if you have any other questions or suggestions. Keep learning new methods and keep growing!
