# How to Find Average of Specific Cells in Excel (3 Handy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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. ### 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. ### 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. ## 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. ### 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. ## 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. ## 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. ## 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. ## Related Articles Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  