How To Average Two Rows In Excel

The Boilerplate function calculates the average of numbers provided equally arguments. To calculate the average, Excel sums all numeric values and divides by the count of numeric values.

Boilerplate takes multiple arguments in the form number1, number2, number3, etc. up to 255 total. Arguments tin include numbers, cell references, ranges, arrays, and constants. Empty cells, and cells that comprise text or logical values are ignored. However, aught (0) values are included. You can ignore zilch (0) values with the AVERAGEIFS function, as explained below.

The AVERAGE function will ignore logical values and numbers entered equally text. If yous 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 Amass office to ignore errors.

Bones usage

A typical mode to use the Boilerplate function is to provide a range, as seen below. The formula in F3, copied downwardly, is:

AVERAGE function basic usage

At each new row, AVERAGE calculates an average of the quiz scores for each person.

Blank cells

The AVERAGE part automatically ignores blank cells. In the screen below, notice cell C4 is empty, and AVERAGE simply ignores it and computes an boilerplate with B4 and D4 only:

AVERAGE function with blank cells

Nevertheless, annotation 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.

AVERAGEIF function exclude zero

Mixed arguments

The numbers provided to AVERAGE tin can be a mix of references and constants:

AVERAGE function with mixed arguments

Boilerplate with criteria

To calculate an average with criteria, apply AVERAGEIF or AVERAGEIFS. In the case beneath, AVERAGEIFS is used to summate the average score for Red and Blue groups:

AVERAGEIFS function with criteria

The AVERAGEIFS function can too apply multiple criteria.

Average superlative 3

By combining the Average part with the Large office, you lot can summate an boilerplate of superlative n values. In the instance beneath, the formula in column I computes an average of the top iii quiz scores in each row:

AVERAGE function top 3

Detailed explanation here.

Weighted average

To calculate a weighted average, you lot'll want to use the SUMPRODUCT function, every bit shown below:

Weighted average with SUMPRODUCT

Read a complete caption here.

Average without #DIV/0!

The boilerplate function automatically ignores empty cells in a set of information. However, if the range contains no numeric values, Average will return a #DIV/0! error. To avoid this problem, y'all tin check the count of values with the COUNT role 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, Boilerplate returns the boilerplate. This case explains this idea in more detail.

Manual average

To summate the average, AVERAGE sums all numeric values and divides past the count of numeric values. This behavior can be replicated with the SUM and COUNT functions manually like this:

                                    =              SUM              (range)              /              COUNT              (range)              // manual average calculation

Notes

AVERAGE automatically ignores empty cells and cells with text values.
Average includes zero values. Utilize AVERAGEIF or AVERAGEIFS to ignore cipher values.
Arguments can exist supplied as constants, ranges, named ranges, or cell references.
Average can handle up to 255 total arguments.
To meet a quick average without a formula, you tin can utilise the status bar.