Fahim Shahriyar Dipto
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
- After that, press 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.
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.
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.
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.
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.
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.
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.
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.
