However, if the range contains no numeric values, AVERAGE will return a #DIV/0! error. The average function automatically ignores empty cells in a set of data. To calculate a weighted average, you'll want to use the SUMPRODUCT function, as shown below: In the example below, the formula in column I computes an average of the top 3 quiz scores in each row:ĭetailed explanation here. Average top 3īy combining the AVERAGE function with the LARGE function, you can calculate an average of top n values. The AVERAGEIFS function can also apply multiple criteria. In the example below, AVERAGEIFS is used to calculate the average score for Red and Blue groups: To calculate an average with criteria, use AVERAGEIF or AVERAGEIFS. The numbers provided to AVERAGE can be a mix of references and constants: Like the AVERAGE function, AVERAGEIF automatically excludes empty cells. In the example below, AVERAGEIF is used to exclude zero values. To exclude zero values, use AVERAGEIF or AVERAGEIFS instead. However, note the zero (0) value in C5 is included in the average, since it is a valid numeric value. In the screen below, notice cell C4 is empty, and AVERAGE simply ignores it and computes an average with B4 and D4 only:

The AVERAGE function automatically ignores blank cells. The formula in F3, copied down, is: =AVERAGE(C3:E3)Īt each new row, AVERAGE calculates an average of the quiz scores for each person. Basic usageĪ typical way to use the AVERAGE function is to provide a range, as seen below.

You can use the AGGREGATE function to ignore errors.

If the values given to AVERAGE contain errors, AVERAGE returns an error. If you need to include these values in the average, see the AVERAGEA function. The AVERAGE function will ignore logical values and numbers entered as text. You can ignore zero (0) values with the AVERAGEIFS function, as explained below. Arguments can include numbers, cell references, ranges, arrays, and constants. Empty cells, and cells that contain text or logical values are ignored. To calculate the average, Excel sums all numeric values and divides by the count of numeric values.ĪVERAGE takes multiple arguments in the form number1, number2, number3, etc. The AVERAGE function calculates the average of numbers provided as arguments.

Average shortcat dildo