How do you use the AVERAGE

The AVERAGE function in Excel is used to calculate the arithmetic mean (average) of a set of numbers. It sums the values in a specified range and then divides by the number of values. This function is helpful for determining the central value or the typical value in a dataset.

Syntax

excel

Copy code

AVERAGE(number1, [number2], ...)

• number1: The first number, cell reference, or range for which you want to find the average (required).
• number2, ...: Additional numbers, cell references, or ranges (optional).

Example 1: Basic Use of AVERAGE

Let’s say you want to calculate the average of the numbers in cells A1, A2, and A3:

excel

Copy code

=AVERAGE(A1, A2, A3)

If A1 contains 10, A2 contains 20, and A3 contains 30, the result will be:

scss

Copy code

=AVERAGE(10, 20, 30) → 20

You can also calculate the average for a range of cells. For example, to find the average of all the numbers in the range A1:A5:

excel

Copy code

=AVERAGE(A1:A5)

If A1:A5 contains 10, 20, 30, 40, 50, the result will be:

scss

Copy code

=AVERAGE(10, 20, 30, 40, 50) → 30

Example 2: Averaging Non-Adjacent Cells

You can average non-adjacent cells by specifying each cell reference or range individually:

excel

Copy code

=AVERAGE(A1, A3, A5)

If A1 is 10, A3 is 30, and A5 is 50, the result will be:

scss

Copy code

=AVERAGE(10, 30, 50) → 30

Example 3: Handling Blank Cells and Text

The AVERAGE function ignores blank cells and cells with text when calculating the average. If the range contains a mix of numbers, blank cells, and text, only the numbers are considered.

For example, if A1:A5 contains 10, 20, "", 40, "Text", the formula:

excel

Copy code

=AVERAGE(A1:A5)

will calculate the average of 10, 20, 40 (ignoring the blank cell and text), resulting in:

scss

Copy code

=AVERAGE(10, 20, 40) → 23.33

Example 4: Using AVERAGE with Conditional Logic

If you want to calculate the average of cells that meet specific criteria, you can use other functions like AVERAGEIF or AVERAGEIFS.

AVERAGEIF: Calculate the Average with a Single Condition

To calculate the average of values in A1:A5 where the value is greater than 20:

excel

Copy code

=AVERAGEIF(A1:A5, ">20")

If A1:A5 contains 10, 25, 30, 35, 40, this will return the average of 25, 30, 35, 40, which is 32.5.

AVERAGEIFS: Calculate the Average with Multiple Conditions

To calculate the average of values in A1:A5 where the value is greater than 20 and less than 40:

excel

Copy code

=AVERAGEIFS(A1:A5, A1:A5, ">20", A1:A5, "<40")

This will return the average of 25, 30, 35, which is 30.

Important Notes:

• The AVERAGE function ignores blank cells and text in the range. If you need to treat blank cells as 0 in the calculation, you can use a different approach like SUM divided by COUNT.
• If you want to average values conditionally, use AVERAGEIF or AVERAGEIFS.

Summary

• AVERAGE: Calculates the average of a range of numbers.
• Usage: =AVERAGE(A1:A5) finds the mean of numbers in the range A1:A5.
• Ignores blanks and text: The function skips blank cells and text.
• Conditional averages: Use AVERAGEIF or AVERAGEIFS to calculate averages based on conditions.

The AVERAGE function is a simple and powerful tool for determining the central tendency of your data!