Calculate the average/mean (AVERAGE function)

To calculate the average/mean for data in Excel, we use the AVERAGE function.

=AVERAGE(number1, [number2]…)

[number1] = Identify the 1st number or cell range to calculate the average for.
[number2] = Not obligatory. Identify when there is a number or cell range other than [number1].

Definition of function A function to calculate the average of a selected number or cell range.
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

Example of use) AVERAGE function

The table below shows the heights of students in a kindergarten. Let us calculate the ‘average height’ in cell E13.

① Calculate the ‘average height’ from all the heights.

(1) [number1] :Highlight cell range [E4:E11].
=AVERAGE(E4:E11)

 

Average for data that meets a condition (AVERAGEIF function)

To add a condition in calculating the average, we use the AVERAGEIF function.

=AVERAGEIF(range, criteria, [average_range])

[range] = Identify the cell range to see if it meets condition.
[criteria] = Input a condition using a numerical value or formula to search in [range].
[average_range] = Identify the actual cell range to find the average for.

Definition of function A function to calculate the average for data which meets a specified condition.
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

Example of use) AVERAGEIF function

Using the same table, let us now calculate the average height of students from ‘class A‘.

① Calculate the ‘average height for class A’ from all the heights.

(1) [range] : Highlight cell range [C4:C11].
(2) [criteria] : Surround the condition class ‘A’ with ” (double quotations). → “A”
(3) [average_range] : Highlight cell range to calculate the average, cell range [E4:E11].
=AVERAGEIF(C4:C11,”A”,E4:E11)

 

POINT!

When the average is calculated, due to the fact that it cannot be fully divided sometimes the result will be displayed long, like ‘123.4567’.

In this case, select the specific cell and click on the [decimal] button under the [Home] ribbon. With the button we can easily increase and decrease the number of decimal units.

 

Average for data that meets multiple conditions (AVERAGEIFS function)

To specify multiple conditions in calculating the average, we use the AVERAGEIFS function.
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2], [criteria2]…)

[average_range] = Identify the actual cell range to find the average for.
[criteria_range1] = Identify the 1st cell range to search if it meets condition.
[criteria1] = Input a condition using a numerical value or formula to search in [criteria_range1].
[criteria_range2] = Not obligatory. Identify the 2nd cell range to see if it meets condition.
[criteria2] = Not obligatory. Input a condition using a numerical value or formula to search in [criteria_range2].

Definition of function A function to calculate the average for data which meets multiple conditions.
EXCEL version Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

Example of use) AVERAGEIFS function

Using the same table, let us now calculate the average height of ‘boys‘ from ‘class A‘.

① Calculate the average height of ‘boys from class A’.

(1) [average_range] : Highlight cell range [E4:E11].
(2) [criteria_range1] : Highlight 1st cell range to see if it meets condition, cell range [C4:C11].
(3) [crieria1] : Surround the condition class ‘A’ with ” (double quotations). → “A”
(4) [criteria_range2] : Highlight 2nd cell range to see if it meets condition, cell range [D4:D11].
(5) [criteria2] : Surround the condition ‘Boy’ with ” (double quotations).→ “Boy”
=AVERAGEIFS(E4:E11,C4:C11,”A”,D4:D11,”Boy”)

 

How to handle empty spaces・0 (zero)/functions to calculate averages

These functions to calculate the average is useful, but we must be careful as “if there is a ‘0’ within the cell range, it will be included when calculating the average“.

Check below for an example.

 

When there is a ‘0 (zero)’ in the cell range

In the table below, Tim, the 2nd student on the table was absent on the day of the medical check up. Therefore, we typed a ‘0 (zero)’ as his height temporarily.
When we calculated the average height in cell E13, while there are more heights over 110cm, the result came out as ‘101.8’.
This is because the ‘0 (zero)’ was also included when calculating the average height.

 

When there is an empty space in the cell range

Instead of typing a ‘0 (zero)’, if we keep the cell empty, the average height will be calculated correctly.

Furthermore, characters like ‘-‘ also do not affect the calculation of average. When there are cells that we ‘cannot input’, we can either keep the cell empty or type characters like ‘-‘.

 

Exclude 0 (zero) when calculating the average

We have explained earlier that if there is a ‘0 (zero)’ in the cell range, it will be included when calculating the average, so we should keep the cell empty or input characters other than numerical values. However, there might be times when we must input ‘0’. Let us look at how to exclude 0 (zero) when calculating the average.

 

Example of use) How to calculate the average of a cell range that includes a ‘0 (zero)’

As before, a ‘0 (zero)’ is typed in the height column of the student absent for the medical check up. Let us try to calculate the average height correctly with this data.

① Calculate the ‘average height’ from all the heights.

(1) Calculate the sum for cell range [E4:E11].

SUM(E4:E11)

(2) Count the number of values other than ‘0 (zero)’ within cell range [E4:E11].

COUNTIF(E4:E11,”<>0″)

(3) Divide the sum calculated in (1) by the number of values other than ‘0 (zero)’ calculated in (2).

SUM(E4:E11) COUNTIF(E4:E11,”<>0″)

Now we have successfully calculated the average, excluding the value ‘0 (zero)’. Remember that we do not necessarily always need to use the AVERAGE function when calculating the average.

 

Use the DAVERAGE function

When calculating the average under multiple conditions, we can also use the DAVERAGE function. This function will identify the whole table including the title row as a ‘database’ and as a result, it can for example “extract the necessary information from the column with the title ‘Gender’…”.

 

=DAVERAGE(database, field, criteria)

[database] = 列の見出し部分も含めた表の範囲を指定します。
[field] = 平均を求めたい見出しを指定します。
[criteria] = 検索条件が入力された範囲を指定します。

Definition of function A function to calculate the average of the ‘field’ that meets a condition.
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

Example of use) DAVERAGE function

In the same table, we added a condition section on the right. We will calculate the average height of all heights that meet the conditions.

① Calculate the average height according to the condition on the right (in class A and a boy).

(1) [database] : Highlight cell range [B3:E11] including the title row.
(2) [field] : Surround the title column to find the average for with ” (double quotations). → “Height (cm)”
(3) [criteria] : Select cell range with condition, cell range [G3:H4]. (※Include the title row as well).
=DAVERAGE(B3:E11,”Height (cm)”,G3:H4)

 

We have successfully calculated the average height for ‘boys from class A’.
As the DAVERAGE function identifies the whole table as a ‘database’ including the titles, we can reflect the conditions by typing them near the table (in the case above, columns G and H). The advantage in using this function is that it is much easier to change the conditions.