How to Find Average If Values Lie Between Two Numbers in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

Inserting AVERAGEIFS function to get the average based on criteria

  • You will get the average value between the values between 85 and 95.

Directly Input Formula to Find Average If Values Lie Between Two Numbers

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.

Insert Formula from AutoSum Command to Find Average If Values Lie Between Two Numbers

  • 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.

Write down suitable criteria to find average lies between two numbers

  • You will figure out the average value between the values between 85 and 95.

Input Formula to Find Average If Values Lie Between Two Numbers

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.

Utilizing AVERAGE and IF Functions to Find Average If Values Lie Between Two Numbers

  • 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.

Applying SUMIFS and COUNTIFS Functions to Find Average If Values Lie Between Two Numbers

  • You will get the average value of the working time between September 3, 2022, to September 7, 2022.

Estimated average value by using SUMIFS and COUNTIFS value

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.

Calculate Average If Values Lie Between Two Dates in Excel

  • 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!


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Soumik Dutta
Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo