One of the most commonly used functions in Excel is the AVERAGE function, which calculates the average of a range of numbers. This blog post will take a closer look at the AVERAGE function and explore how to use it in Excel.
Syntax of Average Function
The syntax for the AVERAGE function is as follows:
AVERAGE(number1, [number2], [number3], …)
Where:
- number1 is the first number in the range to be averaged.
- [number2], [number3] are additional numbers in the range to be averaged.
Examples of Average Functions
Let’s look at a few examples to help illustrate how the AVERAGE function works in Excel.
Example 1: Simple AVERAGE
Consider the following range of numbers:
3, 7, 5, 2
To find the average of this range, we would use the following formula in Excel:
=AVERAGE(3,7,5,2)
Press Enter to get the result. The result would be 4.25.
Example 2: AVERAGE with Cell References
Suppose we have a range of sales figures in a B column for a product, and we want to find the average of these numbers. In this case, we can use cell references instead of listing each number in the formula.
To find the average sales for the product, we would use the following formula in Excel:
=AVERAGE(E2:E18) (where E2:E18 is the range of cells with sales figures).
Press Enter to get the result which will be the average of all the numbers in that range.
Example 3: AVERAGEA Function
The AVERAGEA function is used to calculate the average of a range of numbers while ignoring non-numeric values in the range. Let’s say you have a range of sales and some text values in column D.
To find the average of this range, we would use the following formula in Excel:
=AVERAGEA(D2:D18) (where D2:D18 is the range of cells).
Press Enter to get the result which will be the average of all the numbers in that range, ignoring any text values.
Example 4: Average of Top 3 in a Single Column
To find the average of the top 3 values in a single column, we can use the LARGE function in conjunction with the AVERAGE function. Let’s say you have a range of sales figures in column E, and we want to find the average of the top 3 values.
To find the average of the top 3 values, we would use the following formula in Excel:
=AVERAGE(LARGE(E2:E18, {1,2,3}))
Press Enter to get the result which will be the average of the top 3 values in that range.
Example 5: AVERAGEIF Function
The AVERAGEIF function is used in Excel to calculate the average range of values based on specified criteria. In this example, the data consists of product codes and their respective sales numbers. There are repetitive transactions in the list, and we want to find the average sales of product “B”.
To use the AVERAGEIF function, we first need to specify the range where the product codes are located (in this case, the range is A2 to A9). The criteria for the product code “b” is then added within the function (i.e. “B”). Finally, the range of sales numbers is specified (in this case, it is B2 to B9).
The formula in Excel would look like this: =AVERAGEIF(A2:A9, “B”, B2:B9)
Press Enter to get the result. The result of this formula is 60.33, which is the average of the sales numbers for product “b”.
Conclusion
With these examples in mind, you should have no trouble applying the AVERAGE function to your own work in Excel. Please let us know if you have any questions or comments about this article! We’d be happy to help. Also, check out our video on these functions to better understand how to use them.
