The average function returns the mean value in a range of selected cells.
The AVERAGE function calculates the average of numbers provided as arguments. To calculate the average, Excel sums all numeric values and divides by the count of numeric values. Show
AVERAGE takes multiple arguments in the form number1, number2, number3, etc. up to 255 total. Arguments can include numbers, cell references, ranges, arrays, and constants. Empty cells, and cells that contain text or logical values are ignored. However, zero (0) values are included. You can ignore zero (0) values with the AVERAGEIFS function, as explained below. The AVERAGE function will ignore logical values and numbers entered as text. If you need to include these values in the average, see the AVERAGEA function. If the values given to AVERAGE contain errors, AVERAGE returns an error. You can use the AGGREGATE function to ignore errors. Basic usageA typical way to use the AVERAGE function is to provide a range, as seen below. The formula in F3, copied down, is:
At each new row, AVERAGE calculates an average of the quiz scores for each person. Blank cellsThe AVERAGE function automatically ignores blank cells. In the screen below, notice cell C4 is empty, and AVERAGE simply ignores it and computes an average with B4 and D4 only: However, note the zero (0) value in C5 is included in the average, since it is a valid numeric value. To exclude zero values, use AVERAGEIF or AVERAGEIFS instead. In the example below, AVERAGEIF is used to exclude zero values. Like the AVERAGE function, AVERAGEIF automatically excludes empty cells.
Mixed argumentsThe numbers provided to AVERAGE can be a mix of references and constants:
Average with criteriaTo calculate an average with criteria, use AVERAGEIF or AVERAGEIFS. In the example below, AVERAGEIFS is used to calculate the average score for Red and Blue groups:
The AVERAGEIFS function can also apply multiple criteria. Average top 3By combining the AVERAGE function with the LARGE function, you can calculate an average of top n values. In the example below, the formula in column I computes an average of the top 3 quiz scores in each row: Detailed explanation here. Weighted averageTo calculate a weighted average, you'll want to use the SUMPRODUCT function, as shown below: Read a complete explanation here. Average without #DIV/0!The average function automatically ignores empty cells in a set of data. However, if the range contains no numeric values, AVERAGE will return a #DIV/0! error. To avoid this problem, you can check the count of values with the COUNT function and the IF function like this:
When the count of numeric values is zero, IF returns an empty string (""). When the count is greater than zero, AVERAGE returns the average. This example explains this idea in more detail. Manual averageTo calculate the average, AVERAGE sums all numeric values and divides by the count of numeric values. This behavior can be replicated with the SUM and COUNT functions manually like this:
Notes
Does the average function returns the mean value in a range of selected cells?The AVERAGE function returns the mean value in a range of selected cells.
Which function returns the average of range values?The AVERAGEIF function returns the average of cells in a range that meet criteria you provide.
Which function will return the average value in the selected range of cells in Excel?Returns the average (arithmetic mean) of the arguments. For example, if the range A1:A20 contains numbers, the formula =AVERAGE(A1:A20) returns the average of those numbers.
What is the range of the average function?The AVERAGE function can handle up to 255 arguments, each of which may be a value, cell reference, or range. Only one argument is required, but of course, if you're using the AVERAGE function, it's likely you have at least two.
|