The AVERAGE function in excel is one of the most commonly used functions in Excel and, like all functions in Excel, is fairly intuitive once you understand the necessary inputs. It shouldn’t be much of a surprise that the AVERAGE function does just what you’d imagine it would do: it returns the average (or arithmetic mean) of a given set of numbers. In terms of complexity, the AVERAGE function is actually one of the easier functions to use in Excel because it only consists of one type of argument (input), and that is the numbers (or cell references) for which you are interested in knowing the average.
The AVERAGE Function requires only one argument (or "parameter") but, like the sum function, can take up to 255 total arguments. The AVERAGE function will return the average of all arguments.
=AVERAGE(number_1, [number_2], ...)The AVERAGE function requires at least one numerical argument (number inside the parentheses separated by commas), though finding the average of one number isn't particularly useful.
Each additional argument in the AVERAGE function is optional. The AVERAGE function requires only one argument, but can optionally have any number of additional arguments, up to a maximum of 255 total arguments.
Take a look at the spreadsheet example below. Say your boss asks you to find the average iPhone sales across the three years shown. This is an east task with Excel's AVERAGE function.
The AVERAGE function returns the average of all arguments, or values inside the function parentheses separated by commas. Think of a function's arguments as its inputs. To get the average iPhone revenue across all three years, double-click cell H5 (column H, row 5) in the spreadsheet above and type the following formula:
The AVERAGE formula will return the average of the numbers contained in cells E5, F5, and G5.
Excel will let you keep adding arguments to the function separated by commas, so you can continue to add arguments to the formula above and it will return the average of all of the arguments provided. In fact, while only one argument is required for this function, Excel will let you add up to 255 individual arguments.
Of course, if you're finding the average of many numbers from a range of cells, it's easier to pass the whole range of cells into the AVERAGE function.
Excel can quickly find the average of an entire group of adjacent cells if you pass in a range of cells as an argument to the AVERAGE function. A range of cells is denoted by two cell references with a colon (:) between them. For example, say you wanted to find Apple's average revenue for 2020 in the spreadsheet above. You could type in every cell reference (E5, E6, E7, E8, E9), or you could simply pass in the entire range of cells from E5 to E9 (E5:E9).
Double-click into cell E11 and type this formula to get the average revenue of Apple's product lines in 2020:
This formula should return a value of $54,903, because this is the average of the numbers contained in cells E5, E6, E7, E8, and E9. A range in Excel can contain any number of cells.
In addition to the vertical range we used above (E5:E9), you can also use horizontal ranges of cells with the AVERAGE function. Say you wanted average Mac sales across all three years. Double-click into cell H7 and type the following formula:
Say you wanted to find the average of all iPhone sales, regardless of how many years were included in the spreadsheet. In this case you would want to find the average, or mean, of all numbers in row 5. To do this, you can use the range (5:5) to tell Excel "everything in row 5."
Note that if you use this formula, you cannot write it in row 5, because it will include itself in the average calculation, resulting in a circular reference error.
Similarly, if you wanted to find the average of all the numbers in column H in the example above, you could pass in the range H:H as in the example below:
Just like when using horizontal ranges in Excel, you couldn't write this formula in column H because you would receive a circular reference error.
Finally, if you wanted the average revenue of all products across all three years, you could pass in a two-dimensional range of cells. To find the average of all cells in the spreadsheet example above, double-click into cell H11 and type the following formula:
Finally, Excel lets you mix and match the different types of arguments that you pass to the AVERAGE function. As stated above, AVERAGE can accommodate single cell references as arguments (such as A1), as well as a range of cells (such as A1:B5). AVERAGE can also accommodate plain numbers (such as 5) as arguments, as well as another Excel formula. For example, =AVERAGE(5, A1) is a valid function that will return the average of cell A5 and 5. Additionally, =AVERAGE(5, (10/5)) is another valid function that will return the average of 5 and 2. In fact, any or all of these types of arguments can be used with AVERAGE:
For an extreme example, the following formula:
is a valid function that returns the average of 5, cells A3, A2, B2, C2, D2, and 15. As long as you have 255 arguments or fewer, any numerical value is fair game.
If a certain cell reference or range of cells contains text, Excel will simply ignore the text and only return the average of the cells that contain numerical values.
