Eshrak Kader
Calculating the average of specific cells is a common task in our day-to-day lives and this is where Microsoft Excel excels. In this article, we’ll show 3 ways how 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 B4:D14 cells in the Marks Distribution dataset. Here, the dataset shows the student Names, 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, the D5:D14 cells 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.
Read More: How to Average Values Greater Than Zero in Excel (4 Ways)
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 the C16 cell >> type in the expression given below.
=AVERAGEIF(C5:C14,"*Beta*",D5:D14)
Here, the C5:C14 and the D5:D14 range 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.
Read More: How to Calculate Average of Text in Excel (2 Ways)
1.3 Utilizing 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, therefore just follow along.
📌 Steps:
- Initially, jump to the C17 cell >> insert the following expression in the Formula Bar.
=AVERAGEIF(C5:C14,C16,D5:D14)
In this expression, the C5:C14 and the D5:D14 range represent the Section and Physics columns respectively while the C16 cell point to Section Alpha which given criterion.
Eventually, your output should look like the screenshot shown below.
Read More: How to Calculate Average in Excel (Including All Criteria)
2. Using AVERAGEIFS Function
What if you want to specify more than one condition? Then, the AVERAGIFS function has you covered. The AVERAGEIFS 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 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 the C18 cell and insert the expression given below.
=AVERAGEIFS(D5:D14,C5:C14,C17,D5:D14,">="&85)
In the above formula, the C5:C14 and the D5:D14 range represent the Section and Physics columns, in contrast, the C17 cell point to Section Gamma.
Formula Breakdown:
- 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 Ranges in Excel (3 Methods)
2.2 Combining AVERAGE, LARGE and SMALL Functions to Calculate Top and Bottom 3 Averages
By utilizing the LARGE and SMALL functions we can calculate the top and bottom 3 scores in Physics. Then, using the AVERAGE function we can determine the average output.
📌 Steps:
- First, proceed to the C16 cell and enter the formula given below.
=AVERAGE(LARGE(D5:D14,{1,2,3}))
In this formula, the D5:D14 range represents the Physics column and the {1,2,3} refers to the 3 of the largest values in the Physics column.
Formula Breakdown:
- LARGE(D5:D14,{1,2,3}) → returns the nth largest value in a dataset. Here, the D5:D14 range 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
- Second, move to the C17 cell and type in the following expression.
=AVERAGE(SMALL(D5:D15,{1,2,3}))
In the above formula, the D5:D14 range represents the Physics column and the {1,2,3} refers to the 3 of the smallest values in the Physics column.
Formula Breakdown:
- SMALL(D5:D14,{1,2,3}) → returns the nth smallest value in a dataset. Here, the D5:D14 range 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.
Read More: How to Calculate Average of Top 5 Values in Excel (5 Methods)
Similar Readings
- How to Calculate Sum & Average with Excel Formula
- Ignore #N/A Error When Getting Average in Excel
- How to Calculate Average of Multiple Columns in Excel (6 Methods)
- Add Average Line to Excel Chart (with Easy Steps)
- How to Fix Divide by Zero Error for Average Calculation in Excel
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. Hence, let’s see in detail how we can achieve this.
📌 Steps:
- To start, go to the D17 cell >> insert the formula given below.
=DAVERAGE(B4:E14,B17,C16:C17)
Here, the B4:E14 and C16:C17 range point to the entire dataset, and Section Alpha while the B17 cell shows the Subject of Chemistry.
Formula Breakdown:
- 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 Subject of Chemistry. 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.
Read More: How to Calculate Average Numbers in Excel (9 Handy Methods)
Calculate the Average of Only Cells with Values in Excel
Lastly, we’ll discuss calculating the Average Score of only the cells with values. Luckily, Excel has the built-in 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 >> enter the formula in the Formula Bar.
=AVERAGEA(D5:D14)
Here, the D5:D14 range represents the scores in Physics.
Eventually, the results should look like the screenshot shown below.
Read More: How to Find Average with Blank Cells in Excel (4 Easy Ways)
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 for how 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. And, visit ExcelDemy for many more articles like this.
Related Articles
- How to Average Every Nth Row in Excel (3 Ways)
- How to Average Negative and Positive Numbers in Excel
- [Fixed!] AVERAGE Formula Not Working in Excel (6 Solutions)
- How to Calculate Average of Averages in Excel (with Easy Steps)
- Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)
- How to Calculate Average, Minimum And Maximum in Excel (4 Easy Ways)
