Estimating the average is a pretty regular chore in professional life. There are very few people who have not calculated the average. While finding the average of numbers, Excel provides us with exciting features and formulas. Do you ever want to **calculate average** numbers in Excel? Then fasten your seat belt as we will give you a ride to visualize how to estimate average numbers in Excel in this article. Stay with us and enjoy Excelling.

**Table of Contents**hide

## Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.

## 9 Methods to Average Numbers in Excel

In Excel, there are some extraordinary features and formulas for calculating average numbers. We can also calculate average numbers that match specific criteria. Here, the **Sales **amount is given along with the **Sales Rep’s** name. Now, we want to estimate the average of their sales.

Not to mention we have used the *Microsoft 365* version. You can use any other version at your convenience.

### 1. Utilizing Arithmetic Formula

Basically, the arithmetic formula requires you to calculate the total sum of your dataset and divide it by the number of data points. We can do this operation in 2 ways either in general ways or using the **SUM** and **COUNT** functions. We all know that we can get an average of a dataset by the summation of the numbers divided by the total numbers. It is pretty basic. Though it is a difficult task when you have to handle a large number of datasets. But I’m gonna show you this method with a view to clearing your basic concept.

**📌**** Steps:**

- Firstly, go to cell
**C14**and write down the formula.

**=(C5+C6+C7+C8+C9+C10+C11+C12)/8**

Here, we will add the cells manually and then divide them by **8**, the total number of the dataset.

- Press
**ENTER**and you will get the average.

You can also use the** SUM **and the** COUNT** functions for estimating average numbers. The **SUM** function finds the total amount, and the **COUNT **function returns the number of data points. It will save you time compared to calculating the average manually.

**📌**** Steps:**

- Initially, insert the following formula in the
**C14**cell.

**=SUM(C5:C12)/COUNT(C5:C12)**

Here, the **SUM **function will calculate the sum for the range of **C5:C12 **and the **COUNT **function will count the number of total data for the range **C5:C12**.

- Press
**ENTER**and gradually you will get the result.

**Read More:** **How to Calculate Sum & Average with Excel Formula**

### 2. Using AVERAGE Function

The **AVERAGE function** is quite a simple function in Excel to use. You can simply select the range of the data and calculate the average by using this function. Follow the steps to do it.

**📌**** Steps:**

- First of all, in cell
**C14**, enter the formula.

**=AVERAGE(C5:C12)**

Here, the** AVERAGE** function will average the range **C5:C14**.

- After that, press
**ENTER**and get the result.

**Read More:** **How to Calculate Average True Range in Excel (with Easy Steps)**

### 3. Using AutoSum Feature

Excel provides you with a feature where you can easily get the average. There is an **AutoSum** feature to make your task easy in case of averaging numbers.

**📌**** Steps:**

- Firstly, select the cell where you want to calculate the average.
- Then, go to
**Home**tab >>**⅀****AutoSum**feature >>**Average**.

- A formula will be created automatically.
- Furthermore, select the range of your data.
- Press
**ENTER**.

- Thus, your result is created.

**Read More:** **How to Average a Column in Excel (7 Easy Methods)**

### 4. Applying SUBTOTAL Function

You can easily estimate the average numbers by using the **SUBTOTAL function**. The only thing you need to put is the** function_num** and the data array. Then you can average numbers in the blink of an eye. Please follow the steps.

**📌**** Steps:**

- First of all, move to cell
**C14**and write the formula.

**=SUBTOTAL(1,C5:C12)**

Here, the syntax **SUBTOTAL(1, C5:C12)→ **takes the **function_num** of average, which is **1**, and then selects the entire data range. Thus, the average will be created.

- Finally, we will get the result.

**Read More:** **How to Calculate Average Only for Cells with Values in Excel**

### 5. Using AGGREGATE Function

In the **AGGREGATE function**, you need to assign the **function_num**, the criteria, and the data array. After entering all the arguments properly, you can get the average numbers. It is an easy and time-saving task. Follow the steps.

**📌**** Steps:**

- First of all, insert the formula into your cell where you want to put the average.

**=AGGREGATE(1,4,C5:C12)**

Here, this function will take the **function_num** as **1 **for average, then take the criteria **4** to **Ignore Nothing,** and then take the **Option_array **of **C5:C12**.

- Then, press
**ENTER**. - Gradually, you will get your result.

**Read More:** **How to Ignore #N/A Error When Getting Average in Excel**

### 6. Finding Average of Numbers Including Text in a Cell

Suppose you have a dataset where some texts are included along with the number. You can’t get the actual average in this situation. Here, by providing the **AVERAGEA function**, Excel becomes the savior. It will show you the average, including the text. Go through the steps to get a clear concept.

**📌**** Steps:**

- In the very beginning, go to cell
**G5**and insert the below formula.

**=AVERAGEA(C5:E5)**

Here, the **AVERAGEA **function will give you the output of the dataset range** C5:E5** including the text in the range.

- Afterward, drag down the
**Fill Handle**tool for getting the same formula for other cells.

- Eventually, you will get the average number including text just like the image below.

**Read More:** **How to Calculate Average of Text in Excel (2 Ways)**

**Similar Readings**

**How to Calculate Average of Multiple Columns in Excel (6 Methods)****Calculate Moving Average for Dynamic Range in Excel (3 Examples)****How to Calculate Average Percentage Increase in Excel****Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)****How to Calculate Average Percentage of Marks in Excel (Top 4 Methods)**

### 7. Finding Average of Numbers Excluding Zero

We have almost reached the end of our article. Lastly, we will talk about the matter of estimating** the average number excluding zero**. The normal average formula will count the 0. As a result, the average will be reduced, but if we want to exclude the 0s then we have to use the **AVERAGEIF function**. The steps you need to follow are described below.

**📌**** Steps:**

- First of all, select cell
**G5**and write down the formula.

**=AVERAGEIF(C5:E5,”<>0″)**

Here, the syntax **AVERAGEIF(C5:E5,”<>0″) **checks the 0 in the dataset and then calculates the average excluding the zero.

- Press
**ENTER**.

- Finally, drag down the
**Autofill**tool for other cells and your result will be just like the image below.

**Read More:** **How to Exclude a Cell in Excel AVERAGE Formula (4 Methods)**

### 8. Calculating Average of Numbers with Blank Cells

There can be some blank cells in your dataset. Excel will make the average by counting the blank cells as the active cells. In this case, you will not get the actual average. To avoid these consequences, we will use the **IF** and** COUNT** functions. Follow the steps.

**📌**** Steps:**

- In the very beginning, select cell
**C14**and write the formula.

**=IF(COUNT(C5:C12)>0,AVERAGE(C5:C12),””)**

Here, the syntax **IF(COUNT(C5:C12)>0, AVERAGE(C5:C12),””) **denotes that the** COUNT** function will count the blank cell as it takes the argument **COUNT(C5:C12)>0,** and then calculate the average number by the **AVERAGE** function.

**ENTER**, and your result will be created just like the image below.

**Read More:** **[Fixed!] AVERAGE Formula Not Working in Excel (6 Solutions)**

### 9. Calculating Average of Numbers with Criteria

Sometimes, you may need to average numbers that match specific criteria. Suppose you have a dataset where the data is divided into two groups. Now, you want to estimate the average of each group individually. For doing this, we use the **AVERAGEIFS function**. It is used to average values that match specific criteria. See the steps for better visualization.

**📌**** Steps:**

- First, select cell
**C15**and write the formula.

**=AVERAGEIFS(C5:C12,D5:D12,”White”)**

Here, the **AVERAGEIFS** function will look for the text **White** of** D5:D12** in the data range of **C5:C12**. Then estimate the average.

- Subsequently, go to cell
**C16**and insert the formula.

**=AVERAGEIFS(C5:C12,D5:D12,”Black”)**

Now, it will do the same work for the text **Black **of **D5:D12** and takes their corresponding value for calculating the average. It will return the final average for the **Black **team.

- Eventually, you will get the average under the criteria.

**Read More:** **How to Find Average of Specific Cells in Excel (3 Handy Ways)**

## How to Find the Average of Negative and Positive Numbers in Excel

Suppose you have a dataset where you may have both positive and negative values. Now, you want to get the **average of the positive and negative numbers** individually. It is not a tough job to do. All you need is to enter a simple formula. Let us show you the steps for a better understanding.

**📌**** Steps:**

- Firstly, select the cell
**D14**and write down the formula.

**=AVERAGE(IF(B5:D12<0,B5:D12,””))**

**Formula Breakdown:**

**IF(B5:D12<0, B5:D12,””)→** Checks if the value is negative or not as it takes the argument **B5:D12<0**, then it returns the dataset which is negative.

**AVERAGE(IF(B5:D12<0, B5:D12,””))→ **This function will calculate the average with the values returned by the **IF** function.

- After that, conduct the same procedure in cell
**D15**for the positive value. Whereas, the formula for this operation is stated below.

**=AVERAGE(IF(B5:D12>0,B5:D12,””))**

**Formula Breakdown:**

**IF(B5:D12>0, B5:D12,””)→** Checks if the value is positive or not as it takes the argument **B5:D12>0**, then it returns the dataset which is positive.

**AVERAGE(IF(B5:D12>0, B5:D12,””))→** Mainly, this function will calculate the average with the values returned by the **IF** function.

- Thus, you will get the average for positive and negative numbers individually.

## How to Find Average of Top and Bottom Numbers in a Cell Range

The second criterion we have presented here is to find the average of the top 3 and bottom 3 numbers of the dataset. We have used the** LARGE** and **SMALL** functions in this case.

**📌**** Steps:**

- Firstly, select the
**C14**and enter the simple formula.

**=AVERAGE(LARGE(C5:C12,3))**

Here, the** LARGE** function will find the top 3 largest values among the dataset, as we have entered argument 3. Then the **AVERAGE** function will calculate the average.

Thus, you will get the result for the top 3 values of the dataset.

- Now, we will do the same thing for the bottom 3 values as well. Go to cell
**C14**and write up the formula.

**=AVERAGE(SMALL(C5:C12,3))**

Here, the** SMALL** function will find the top 3 smallest values among the dataset, as we have entered argument 3. Then the **AVERAGE** function will calculate the average.

- Subsequently, press
**ENTER,**and your result will be displayed.

**Read More:** **How to Calculate Average of Top 5 Values in Excel (5 Methods)**

## How to Calculate Weighted Average Using SUMPRODUCT Function

Basically, we can also calculate the weighted average in Excel. It means we will use some portion/percentage of the inserted data to make the average. For doing this operation, we will use the **SUMPRODUCT function**. Though it is an advanced process in Excel, following the steps below will make your concept clear.

**📌**** Steps:**

- First of all, select cell
**G5**for entering the formula.

**=SUMPRODUCT($D$16:$F$16,C5:E5)/SUM($D$16:$F$16)**

Here,

**$D$16:$F$16**= The subject-wise weightage we want to calculate.

**C5:E5**= The subject-wise mark for each student.

**Formula Breakdown**:

**SUMPRODUCT($D$16:$F$16, C5:E5)→ **This function will first take the weightage of each subject and then multiply it with their individual data.

**SUM($D$16:$F$16)→ **This function calculates the weightage value of the range** $D$16:$F16**.

**SUMPRODUCT($D$16:$F$16, C5:E5)/SUM($D$16:$F$16)→ **Finally, the result of the **SUMPRODUCT** function will be divided by the result of the **SUM** function.

- Finally, you will get your weighted average.

**Read More:** **How to Fix Divide by Zero Error for Average Calculation in Excel**

## Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

## Conclusion

That’s all about today’s session. And these are some easy methods to average numbers in Excel. Please let us know in the comments section if you have any questions or suggestions. For your better understanding please download the practice sheet. Visit our website **ExcelDemy**, a one-stop Excel solution provider, to find out diverse kinds of excel methods. Thanks for your patience in reading this article.