Excel AVERAGE() FunctionMS Excel is a widely popular spreadsheet program that comes inbuilt with the Microsoft Office package. Excel has a wide range of supported functions to help us perform operations on the data within the sheet. It has more than 400 functions, and the AVERAGE() function is one of the most used functions in Excel. In this article, we discuss the process of using the Excel AVERAGE() function, including its syntax and examples. What is the AVERAGE Function in Excel?The AVERAGE function is one of the popular builtin functions in Excel. It is found in a category of Excel Statistical Functions. The AVERAGE function is typically used to retrieve the average value or arithmetic mean from the supplied range or cells of numerical values. The function can accept series of numbers and calculate the arithmetic mean of the supplied arguments accordingly. The AVERAGE function can be easily used as a typical worksheet function in Excel. This means that Excel allows us to apply the AVERAGE function as part of a formula to one or more desired cells within the sheet. The AVERAGE function is inbuilt in all the versions of MS Excel, including the latest Office 365, Excel 2019, Excel 2016, Excel 2013, etc. The AVERAGE function is useful in financial analysis and can determine the mean value (average) of the series of numbers in many conditions. For instance, we can calculate the average sales of any product for the previous six months or more in a business. SyntaxThe following is the syntax of the AVERAGE function in Excel: In this syntax, we can identify the numbers using the set of one or more numeric values, named range, arrays, a reference to the particular cell, or a range of numbers. Arguments or ParametersThe AVERAGE function must be used with at least one argument, meaning that one argument is compulsory to be supplied to this function. Apart from this, the rest of the arguments are optional. They can be either supplied or ignored to the AVERAGE function based on the requirements. The AVERAGE function has the following arguments:
In older versions of Excel, such as Excel 2003 and earlier, the AVERAGE function accepts up to a maximum of 30 arguments. However, the limit was increased in later versions. Thus, the later versions (Excel 2007 and higher) allow us to use up to 255 individual arguments in the AVERAGE function. Moreover, we can define each argument as an array of values or a range of cells, further containing many other values. ReturnsThe AVERAGE function in Excel returns the average numeric value from the selected/ supplied set of data cells/ ranges of numbers. How to use AVERAGE Function in Excel?The AVERAGE function is one of the most basic and easytouse builtin functions. We can use this particular function as a usual worksheet function in an Excel sheet. This determines that we can apply the AVERAGE function as a part of the formula in one or more desired cells in the sheet. To use the AVERAGE function in its simplest form within the Excel sheet, we must start typing it in the desired cell and provide random numeric values as arguments. For example: In this example, the AVERAGE function returns the value 2 as an output because it is the average value of all the three supplied arguments. However, it is rarely seen that we need to deal with the values directly in the function as arguments. Instead, we typically use operations on values stored in cells or ranges. In such a case, we need to perform the below steps to use the AVERAGE function and calculate the average value from the specified or selected range of cells/ data:
Suppose we have the following excel sheet, and we want to calculate the average value of the values stored in column A. Since we have effective values in the range of cells from cell A1 to cell A8, we apply the AVERAGE function only for this specific range. If we consider cell A9 as the result cell, we use the AVERAGE function in cell A9 like in the following image: Lastly, we press the Enter key, and the function returns the average value for the specified range in a result cell: Let us now understand the different use cases of the AVERAGE function with the help of the following examples: Excel AVERAGE Function ExamplesThe following examples discuss the process on how we can use the AVERAGE function in different ways: Example 1: Calculating the Average Marks We can calculate the arithmetic mean or average of some values or marks written in an Excel sheet. For this, we need to select the range of marks for which we want to find out the results and apply the AVERAGE function on it. We can select the result cell accordingly before we apply the AVERAGE function. For instance, let us consider the following Excel sheet with some marks given to employees in an interview. We want to calculate the average marks achieved by each employee. We can perform the following steps to obtain a mean or average marks for each employee:
Example 2: Calculating the Average Sales The AVERAGE function is used in most financial sectors to find the average sales and total average revenue for a specific period. This function makes calculating average sales or revenue easy when related data is stored in an Excel sheet. The main advantage of using the AVERAGE function in the excel sheet is that we can update the data whenever required and get the corresponding average result in realtime. For instance, let us consider the following Excel sheet with some monthly sales data for any company. The overall sales data is divided into four different zones. Now, we want to calculate the average sales for each month and each zone. We can perform the following steps to obtain a mean or average sales for each month and each zone:
Example 3: Calculating the Average for Initial (top) n Values We can use the AVERAGE function to calculate the average value for top n numerical values. It can be performed by combining the AVERAGE function with the LARGE function. For instance, let us consider the following Excel sheet with some marks given to employees in an interview. We want to calculate the average marks achieved by each employee only in the top three rounds without including the practical marks. In the above example, we can usually find out the average marks for each student in all rounds and a practical by using the process discussed in example 1. However, to calculate the average marks only for the top three rounds, we need to use the AVERAGE function with the LARGE function like this: =AVERAGE (LARGE(C3:F3, {1, 2, 3} ) ) Upon pressing the Enter key, the function returns the average value of the top three marks scored by an employee out of all four marks, which is shown below: Now, we have obtained the average marks of the top three rounds for the first employee. Next, we need to drag a result cell to the rest of the result cells. This will provide the average result of the top three marks for all the other employees. That is how we can use the AVERAGE function in Excel. Some Important Points to RememberTo successfully apply the AVERAGE function in Excel, we must remember the following facts:
Next TopicExcel MIN() Function
