AVERAGE Formula in Excel: How to Calculate AVERAGE in Excel?
The AVERAGE formula is one of the most commonly used mathematical operations on the spreadsheet program, MS Excel. What is not commonly known is that the AVERAGE formula is a versatile operation that allows you to study and analyze large volumes of data. The AVERAGE formula in Excel has three variants that can help you calculate the average based on pre-specified conditions.
In this blog, we will go through how to use the AVERAGE function in Excel. We will also take a look at the weighted AVERAGE formula, the AVERAGEIF formula, and the AVERAGEA function in MS Excel.
What is the AVERAGE Function in Excel?
The AVERAGE function in Excel performs statistical calculations. The average function figures out what an array of data’s arithmetic mean is. In other words, it sums up all the values of the variable under study and divides them by the total number of values.
The average function’s formula is =AVERAGE (number1, [number2],…)
How to Calculate AVERAGE in Excel?
To apply the AVERAGE function in Excel sheet, follow these steps:
- Select the cell where you want to display the result.
- Type the equals sign (=) to start the formula.
- Type “AVERAGE” followed by an opening parenthesis “(“.
- Select the range of cells you want to include in the calculation.
- Type a closing parenthesis “)” to close the function.
- Press Enter.
- We have the final answer in B9.
To learn more about the AVERAGE formula in Excel sheets, you can consider pursuing an advanced Excel course.
Also Read: How To Use VLOOKUP In Excel
How to Use Weighted AVERAGE Formula in EXCEL?
The “SUMPRODUCT” function in Excel can be used to compute a weighted average by multiplying each value by its associated weight, adding all of the values together, and then dividing the result by the sum of the weights. Here’s an illustration:
Imagine you want to calculate the weighted average and you have a list of test results together with the corresponding weights for each test:
To calculate the weighted average of these scores, you can use the following formula:
=SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4)
Where A2:A4 contains the test scores and B2:B4 contains the weights. This formula multiplies each score by its weight, adds the products together, and divides by the sum of the weights to get the weighted average.
In this example, the weighted average would be:
(800*30 + 900*40 + 70*30) / (30+40+30) = 81.0 as seen in B5 below
What is the AVERAGEIF Function?
This command adds a condition to the average calculation. A given criterion or set of criteria can be used to calculate the average using this formula. With the help of the AVERAGEIF function, this is achievable.
The AVERAGEIF function enables us to determine the average (arithmetic mean) of the cells that satisfy a specific requirement.
AVERAGEIF Function Syntax = AVERAGEIF (range, criterion, [average range])
- The cells in which the criteria will be searched are referred to as the range.
- Criteria is the condition or requirement expressed as a number, expression, or text that establishes which cells will be used to calculate the average.
- The average will be calculated using the average range of cells. If left out, the average is computed by the range’s cells.
Also Read: Basic MS Excel Formulas
How to Use the AVERAGEIF Function?
The AVERAGEIF function in Excel is used to calculate the average of a range of cells that meet a certain criterion or condition. Here’s an example of how to use the AVERAGEIF function:
- Suppose you have a list of test scores in cells A2 through A10, and you want to calculate the average of scores that are greater than or equal to 80. You can use the AVERAGEIF function to do this. Here are the steps:
- Select the cell where you want to display the result, for example, cell B2.
- Type the AVERAGEIF function followed by an opening parenthesis “(“, like this: “=AVERAGEIF(“.
- Enter the range of cells that you want to evaluate, in this case, A2:A10, followed by a comma.
- Enter the condition that you want to use to determine which cells to average, in this case “>=80”, followed by a comma.
- Enter the range of cells whose corresponding values should be averaged, in this case, A2:A10.
- Type a closing parenthesis “)” to close the function.
- The formula should look like this: =AVERAGEIF(A2:A10,”>=80″,A2:A10)
- Press Enter. The result would be displayed in cell B2 and would show the average of the test scores that are greater than or equal to 80.
Important Points to note:
- AVERAGEIF ignores empty cells in its range
- If the range is a blank or text value, or if no cells meet the criteria, AVERAGEIF returns an error message #DIV/0!
- However, if a criteria cell is empty, AVERAGEIF treats it as a 0 value
What is AVERAGEA Function?
AVERAGEA is an Excel function that helps to calculate the average of a range of values that can include text, number, and logical values.
It’s important to keep in mind that the AVERAGEA function treats logical and text variables exactly like numbers. The AVERAGEA function, for instance, will treat the text “10” and the logical value TRUE as the numbers 10 and 1, respectively, and include them in the calculation.
The syntax for AVERAGEA is –
=AVERAGEA(value1, [value2], …)
How to Use AVERAGEA Function?
Let’s look at an example to understand how the AVERAGEA function works –
- Assume that you have a dataset of student grades containing both numbers and letters in cells B1 to B10. If you want to calculate the average of all grades, to do this use the AVERAGEA function.
- Select the cell where you want to display the result, for example, let’s take B11.
- Type the AVERAGEA function, i.e, =AVERAGEA, and select the cell range that you wish to calculate. The final formula should be like =AVERAGEA(B1:B10)
- Press Enter, and the result will be displayed in cell B11.
Conclusion
This blog must have given you a basic understanding of the AVERAGE, AVERAGEIF, and AVERAGEA functions of Excel. You should practice utilizing some datasets to strengthen your comprehension and develop useful Excel average knowledge. Practicing these functions simultaneously will help you better understand the AVERAGE formula in Excel.
