In this article, we’ll show 3 ways to find the average of specific cells in Excel. In addition, we’ll also discuss calculating the average from only the cells with values. Henceforth, let’s have a glance at each method with simple and easy illustrations.
Now, let’s consider the cells B4:D14 in the Marks Distribution dataset. Here, the dataset shows the student’s Name, their Section, and their scores in Physics respectively.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.
1. Using the AVERAGEIF Function to Find the Average of Specific Cells in Excel
Firstly, let’s acquaint ourselves with the AVERAGEIF function. The AVERAGEIF function computes the arithmetic mean of the cells specified under a given condition or criterion. Just have a close look at the following three sub-methods.
1.1 Using AVERAGEIF and Comparison Operator
For our first method, we’ll find the average of the Physics scores that are greater than or equal to 75 using the comparison operator, so just follow the steps.
📌 Steps:
- In the first place, go to the C16 cell and enter the formula below.
=AVERAGEIF(D5:D14,">="&75)Here, cells D5:D14 represent the marks in Physics while the “>=”&75 specify the criterion which is greater than or equal to 75.
Finally, the results should look like the image given below.
1.2 Applying AVERAGEIF to Match Specific Text
You can also specify text within the AVERAGEIF function to return the average of only those cells matching the criterion. Here, we’ll calculate the average score in Physics of Section Beta, hence, let us see the procedure in detail.
📌 Steps:
- First and foremost, move to cell C16 and type the formula given below.
=AVERAGEIF(C5:C14,"*Beta*",D5:D14)Here, ranges C5:C14 and D5:D14 refer to the Section and Physics columns respectively. Meanwhile, the “*Beta*” represents the criteria to match. As a note, the asterisk (*) character before and after Beta indicates an exact match.
Finally, the results should look like the picture given below.
1.3 Using AVERAGEIF with Single Criteria
Next, we’ll find the average score while specifying a single condition. Here, we want to know the Average Score of the students in Section Alpha.
📌 Steps:
- Jump to cell C17 and insert the following formula.
=AVERAGEIF(C5:C14,C16,D5:D14)In this expression, ranges C5:C14 and D5:D14 represent the Section and Physics columns respectively. Cell C16 points to Section Alpha which is given criterion.
Eventually, your output should look like the screenshot shown below.
Read More: How to Calculate Average of Multiple Ranges in Excel
2. Using AVERAGEIFS Function
What if you want to specify more than one condition? Then, the AVERAGIFS function has you covered. The AVERAGEIFS function can take multiple criteria from different rows and columns to return the average value. Now, allow me to demonstrate the process in the steps below.
2.1 Employing AVERAGEIFS with Double Criteria
Another way to find the average of specific cells in Excel involves using the AVERAGEIFS function. In this case, we’ll specify two conditions which are Score is greater than 85 and Section Gamma and we want to obtain the Average Score of the students who meet these criteria.
📌 Steps:
- To begin with, navigate to cell C18 and insert the formula given below.
=AVERAGEIFS(D5:D14,C5:C14,C17,D5:D14,">="&85)- AVERAGEIFS(D5:D14,C5:C14,C17,D5:D14,”>=”&85) → finds average for the cells specified by a given set of conditions or criteria. Here, D5:D14 is the average_range argument which is the Physics column. Next, C5:C14 is the criteri_range1 argument which refers to the Section column and the C17 is the criteria1 argument which is Section Gamma. Following this, D5:D14 is the criteri_range2 argument which refers to the Physics column, and the “>=”&85 is the criteria2 argument which represents the values greater than and equal to 85.
- Output → 88.0
Consequently, the results should look like the image given below.
Read More: How to Calculate Average of Multiple Columns in Excel
2.2 Combining AVERAGE, LARGE, and SMALL Functions to Calculate Top and Bottom 3 Averages
By using the LARGE and SMALL functions we can calculate the top and bottom 3 scores in Physics. Using the AVERAGE function, we can determine the average output.
📌 Steps:
- First, proceed to cell C16 and enter the formula given below.
=AVERAGE(LARGE(D5:D14,{1,2,3}))- LARGE(D5:D14,{1,2,3}) → returns the nth largest value in a dataset. Here, range D5:D14 represents the Physics column. Next, the {1,2,3} refers to the 3 of the largest values in the Physics column.
- Output → 89, 88, 87
- AVERAGE(LARGE(D5:D14,{1,2,3})) → becomes
- AVERAGE(89, 88, 87)  → returns the average of the arguments. Here, the values of 89, 88, and 87 are summed and divided by 3 to return their respective average.
- Output → 88.0
- Next, move to cell C17 and type in the following formula.
=AVERAGE(SMALL(D5:D15,{1,2,3}))- SMALL(D5:D14,{1,2,3}) → returns the nth smallest value in a dataset. Here, range D5:D14 represents the Physics column. Next, the {1,2,3} refers to the 3 of the smallest values in the Physics column.
- Output → 60, 62, 73
- AVERAGE(SMALL(D5:D14,{1,2,3})) → becomes
- AVERAGE(60, 62, 73)  → returns the average of the arguments. Here, the values of 60, 62, and 73 are summed and divided by 3 to return their respective average.
- Output → 65.0
Subsequently, the result should look like the picture given below.
3. Using DAVERAGE Function to Find Average of Specific Cells in Excel
Excel’s DAVERAGE function can determine the average of cells with numeric values, given the proper criteria, from databases. Here, we want to compute the Average Score for Chemistry in Section Alpha.
📌 Steps:
- To start, go to cell D17 and insert the formula given below.
=DAVERAGE(B4:E14,B17,C16:C17)- DAVERAGE(B4:E14, B17, C16:C17) → averages the values in a database that match the specified conditions. Here, B4:E14 is the database argument that represents all the cells in the dataset. Next, B17 is the field argument, which refers to the Chemistry subject. Lastly, the C16:C17 is the criteria argument, which is Section Alpha.
- Output → 81.5
Lastly, your output should appear as the image shown below.
Calculate the Average of Only Cells with Values in Excel
Lastly, we’ll discuss calculating the average score of cells with values only. Luckily, Excel has the AVERAGEA function which returns the arithmetic mean of all the non-blank cells in the selected range. So, let’s begin.
📌 Steps:
- At the very beginning, jump to the C16 cell and enter the formula.
=AVERAGEA(D5:D14)Here, range D5:D14 represents the scores in Physics.
Eventually, the results should look like the screenshot shown below.
Read More: How to Average Only Visible Cells in Excel
Practice Section
We have provided a practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.
Download Practice Workbook
You can download the practice workbook from the link below.
Conclusion
In this article, we’ve shown you 3 effective methods to find the average of specific cells in Excel. I suggest you read the full article carefully and apply the knowledge to your needs. You can also download our free workbook to practice. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.
Related Articles
- How to Average a Column in Excel
- How to Find Average with Blank Cells in Excel
- How to Exclude a Cell in Excel AVERAGE Formula
- How to Average Every Nth Row in Excel
- How to Fix Divide by Zero Error for Average Calculation in Excel
- How to Ignore #N/A Error When Getting Average in Excel
- [Fixed!] AVERAGE Formula Not Working in Excel
<< Go Back to Conditional Average | Calculate Average | How to Calculate in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
