Articles

Excel statistical functions for calculating averages: Tutorial with examples, part two

Excel provides a wide range of statistical functions that perform calculations from basic mean, median, and mode to more complex statistical distributions and probability tests.

In this article we will delve into Excel's statistical functions for calculating the average.

Please note that some statistical functions were introduced in recent versions of Excel and are therefore not available in older versions.

Table of Contents: List of functions

Functions for calculating averages

`AVERAGE`

The function AVERAGE is one of Excel's statistical functions. The function returns the average of the numeric values entered into the function. In simple words, it adds all the values specified in the function, then divides them by the count and returns the result.

Syntax

= AVERAGE(number1,number2,…)

topics

numero1 : The first number you want to use for calculating the average.
[numero2] : The second number you want to use for averaging.

Example

To see how the function works AVERAGE let's see an example:

In the first example we inserted the arguments directly into the function.

In the second example, we referenced a range containing numbers. You can refer to the unbounded cell using a continuous range and if you want to refer to a dynamic range you can use a table for that.

You can refer to the unbounded cell using a continuous range and if you want to refer to a dynamic range you can use a table.

In the third example we referred to a range in which the cells are formatted as text values. In this case, you can convert those text numbers to real numbers to calculate the average.

In the fourth example we have an apostrophe before each value in each cell and therefore ignored by the function.

`AVERAGEA`

The function AVERAGEA of Excel is listed in the Microsoft Excel Statistical Functions category. Returns the average of the specified numbers in function, but unlike AVERAGE, treats Boolean values and numbers formatted as text.

Syntax

=AVERAGEA(valore1,valore2,…)

topics

value1 : A value that is a number, a logical value, or a number stored as text.
[valore2] : A value that is a number, a logical value, or a number stored as text.

Example

To understand the function AVERAGEA we need to see an example:

The value returned by the function is 10,17 which is “(0+0+1+10+20+30)/6”.

`AVERAGEIF`

The function AVERAGEIF of Excel is listed in the Microsoft Excel Statistical Functions category. Returns the average of numbers that satisfy multiple specified conditions .

Syntax

= AVERAGEIF( range, criteria, [average_range] )

Topics

range: an array of values (or a range of cells containing values) to test against the provided criteria.
criteria: The condition to be tested against each of the values in the provided range.
[average_range]: An optional array of numeric values (or cells containing numbers) that should be averaged if the corresponding value in the range meets the criteria provided.

If the topic [average_range] is omitted, the average is calculated for the values in the initial provided range.

The criteria provided can be:

a numeric value (including integers, decimals, dates, times, and logical values) (for example, 10, 01/01/2008, TRUE)
O
a text string (e.g. “Text”, “Thursday”) – MUST be provided in quotes
O
an expression (e.g. “>12”, “<>0”) – MUST be provided in quotes.
Also note that the function AVERAGEIF Excel is not case sensitive. So, for example, the text strings “TEXT"and "text” will be evaluated as equal.

Example

To understand the function AVERAGEIF we have to try it in an example.

The cells A16-A20 of the following spreadsheet show five examples of the function AVERAGEIF in Excel.

For each function call AVERAGEIF of Excel, the topic range (to be tested against criteria) is the range of cells A1-A14 and the topic [average_range] (containing the values to be averaged) is the range of cells B1-B14.

Note that, in cells A16, A18, and A20 of the spreadsheet above, the text value “Thursday” and the expressions “>2” and “<>TRUE” are enclosed in quotation marks. This is essential for all texts or expressions.

`AVERAGEIFS`

The function AVERAGEIFS of Excel is listed in the Microsoft Excel Statistical Functions category. Returns the average of numbers that satisfy multiple specified conditions . Unlike AVERAGEIF, you can set multiple conditions and calculate the average only for numbers that meet all conditions.

Syntax

= AVERAGEIFS( average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )

Topics

average_range: An array of numeric values (or cells containing numbers) that are to be averaged.
criteria_range1, [criteria_range2], …: Arrays of values (or ranges of cells containing values) to test against each other criteria1, criteria2, … (The arrays criteria_range supplied must all have the same length).
criteria1, [criteria2], …: The conditions to be tested with respect to the values in criteria_range1, [criteria_range2], …

Example

Now let's look at an example of the function AVERAGEIFS:

In the following example, we have used the function AVERAGEIFS to calculate the average quantity sold by the seller “Pietro” and for the product “B”. We entered the criteria directly into the function and have two entries of Peter's sale of product B.

In the following example, we have used AVERAGEIFS with an asterisk to calculate the average price of fruit whose quantity is greater than 20 units and has B in the name.

In the data below, we have two fruits that meet these criteria.

`MEDIAN`

The function MEDIAN Excel returns the statistical median (the average value) of a list of supplied numbers.

Syntax

= MEDIAN( number1, [number2], ... )

Topics

numeric arguments are a set of one or more numeric values (or arrays of numeric values), for which you want to calculate the median

Note that:

If there are an even number of values in the given dataset, the average of the two average values is returned;
If a supplied array contains blank cells, text, or logical values, these values are ignored when calculating the median.
In current versions of Excel (Excel 2007 and later), you can supply up to 255 numeric arguments to the Median function, but in Excel 2003 the function can only accept up to 30 numeric arguments. However, each of the numeric arguments can be an array of many values.

Example

The following spreadsheet shows three examples of the function Median:

Consider that, in the previous examples:

The example in the cell B2 receives an even number of values and therefore the median is calculated as the average of the two mean values, 8 and 9;
The example in the cell B3 includes the empty cell A8. This cell is ignored when calculating the median.

For more details on the function MEDIAN of Excel, see the Microsoft Office website .

`MODE`

The function MODE of Excel returns the MODE statistic (the most frequent value) of a list of supplied numbers. If there are 2 or more recurring values in the supplied data, the function returns the lowest value among them

Syntax

= MODE( number1, [number2], ... )

Topics

are a set of one or more numeric values (or arrays of numeric values), for which you want to calculate the MODE statistics.

Note:

In current versions of Excel (Excel 2007 and later), you can supply up to 255 numeric arguments to the function MODE, but in Excel 2003 the function can only accept up to 30 numeric arguments.
Text and logical values within a provided array of numbers are ignored by the function Mode.

Function examples MODE

Example 1

The following spreadsheet shows the function MODE Excel, used to calculate the MODE statistics of the set of values in the cells A1-A6.

Example 2

The following spreadsheet shows the function MODE, used to calculate the MODE statistics of the set of values in the cells A1-A10.

Note that in this case there are two mode in the data.

In the above case, where the data in column A of the previous spreadsheet has two MODE statistics (3 and 4), the function MODE returns the lower of these two values.

For further details and examples of the function MODE of Excel, see the Microsoft Office website .

`MODE.SNGL`

The function MODE.SNGL of Excel returns the MODE statistic (the most frequent value) of a list of supplied numbers. If there are 2 or more recurring values in the supplied data, the function returns the lowest value among them.

The function Mode.Sngl is new in Excel 2010 and therefore not available in earlier versions of Excel. However, the function is simply a renamed version of the function MODE available in earlier versions of Excel.

Syntax

= MODE.SNGL( number1, [number2], ... )

Topics

are a set of one or more numeric values (or arrays of numeric values), for which you want to calculate the MODE.SNGL statistics.

Function examples MODE.SNGL

Example 1

The following spreadsheet shows the function MODE.SNGL Excel, used to calculate the statistical MODE of the set of values in the cells A1-A6.

Example 2

The following spreadsheet shows the function MODE.SNGL, used to calculate the statistical mode of the set of values in cells A1-A10.

Note that in this case there are two mode in the data.

In the above case, where the data in column A of the previous spreadsheet has two MODE statistics (3 and 4), the function MODE.SNGL returns the lower of these two values.

For further details and examples of the function MODE.SNGL of Excel, see the Microsoft Office website .

`GEOMEAN`

The Geometric Mean is a measure of the average that indicates the typical value of a set of numbers. This measurement can only be used for positive values.

The geometric mean of a set of values, y ₁ , and ₂ , …, there _n it is calculated with the formula:

Note that the geometric mean is always less than or equal to the arithmetic mean.

The function Geomean Excel calculates the geometric mean of a given set of values.

Syntax

= GEOMEAN( number1, [number2], ... )

Topics

one or more positive numeric values (or arrays of numeric values), for which you want to calculate the geometric mean.

In current versions of Excel (Excel 2007 and later), the function can accept up to 255 numeric arguments, but in Excel 2003 the function can only accept up to 30 numeric arguments. However, each argument can be an array of values or a range of cells, each of which can contain many values.

Example

The cell B1 of the spreadsheet shows a simple example of the function geomean in Excel, used to calculate the geometric mean of the values in cells A1-A5.

In this example, the Geomean function returns the value 1.622671112 .

`HARMEAN`

The harmonic mean is a measure of the mean calculated as the reciprocal of the arithmetic mean of the reciprocals. This can only be calculated for positive values.

The harmonic mean of a set of values, y1, y2, ..., yn is therefore given by the formula:

the harmonic mean is always less than or equal to the geometric mean and the geometric mean is always less than or equal to the arithmetic mean.

The function Harmean Excel calculates the harmonic mean of a given set of values.

Syntax

= HARMEAN( number1, [number2], ... )

Topics

one or more positive numeric values (or arrays of numeric values), for which you want to calculate the harmonic mean.

Example

Cell B1 in the spreadsheet on the right shows a simple example of the function Harmean in Excel, used to calculate the harmonic mean of the values in cells A1-A5.

In this example, the function Harmean returns the value 1.229508197.

`TRIMMEAN`

The function TRIMMEAN (also known as the trimmed mean) is a measure of the mean that indicates the central tendency of a set of values.

The trimmed mean is calculated by discarding some values at the ends of the range of values, before calculating the arithmetic mean of the remaining values. This prevents the calculated average from being distorted by extreme values (also known as outliers, technically outliers).

Syntax

= TRIMMEAN( array, percent )

Topics

array – An array of numeric values for which you want to calculate the truncated mean.
percent – The percentage of values you want to delete fromarray provided.

Note that the percentage value specified is the total percentage of values to exclude from the calculation. This percentage is divided by two to get the number of values removed from each end of the range.

It should also be noted that when Excel calculates how many values deleted from thearray of values provided, the calculated percentage is rounded down to the nearest multiple of 2. For example, if you want to calculate the trimmed mean of a array of 10 values, therefore:

A percentage of 15% corresponds to 1,5 values, which will be rounded down to 0 (i.e. no values will be discarded from thearray before calculating the average);
A percentage of 20% corresponds to 2 values, so 1 value will be discarded from each end of the range before averaging the remaining values;
A percentage of 25% corresponds to 2,5 values, which will be rounded down to 2 (that is, 1 value will be discarded from each end of the range before averaging the remaining values).

Example

The cells B1-B3 in the spreadsheet below show 3 examples of the function trimmean in Excel, all used to calculate the trimmed mean of the values in the cells A1-A10, for different percentage values.

Keep in mind that, in the cell B1 of the spreadsheet above, the percentage argument given is 15%. Since in thearray provided there are 10 values, the number of values to ignore is 1,5 rounded down to the nearest multiple of 2 which is zero.

Functions for calculating Permutations

`PERMUT`

The number of permutations for a given number of objects is the number of combinations in any possible order.

Permutations differ from combinations in that, for a permutation, the order of the objects matters, but in a combination the order does not matter.

The number of possible permutations is given by the formula:

where k is the number of objects chosen e n is the number of possible objects.

The Excel function Permut calculates the number of permutations of a specified number of objects from a set of objects.

Syntax

= PERMUT( number, number_chosen )

Topics

number: The total number of items available
number_chosen: The number of objects in each permutation (i.e. the number of objects selected from the set)

Note that if any of the arguments are given as decimal values, they will be truncated to integers by the function Permut.

Example

In the following spreadsheet, the Excel Permut is used to calculate the number of permutations of six objects, selected from sets of different sizes:

`PERMUTATIONA`

Excel functions exchange and Permutationa both calculate the number of permutations of a selection of objects from a set.

However, the two functions differ in that the Permut. function does not count repetitions while the Permutationa function counts repetitions.

For example, in a set of 3 objects, a , b , c , how many permutations are there of 2 objects?

La Permut. function returns the result 6 (permutations: ab , ac , ba , bc , ca , cb );
The Permutationa function returns the result 9 (permutations: aa , ab , ac , ba , bb , bc , ca , cb , cc ).

The Excel function Permutationa calculates the number of permutations of a specified number of objects from a set of objects.

Syntax

= PERMUTATIONA( number, number_chosen )

Topics

number: The total number of objects in the set (must be ≥ 0).
number_chosen: The number of objects selected from the set (must be ≥ 0).

Note that if any of the arguments are given as decimal values, they will be truncated to integers by the function PERMUTATIONA.

Example

In the following spreadsheet, the Excel PERMUTATIONA is used to calculate the number of permutations of six objects, selected from sets of different sizes:

Functions for calculating confidence intervals

`CONFIDENCE`

In Excel 2010, the function CONFIDENCE has been replaced by the function Confidence.Norm.

Although it has been replaced, current versions of Excel still have the feature Confidence (stored in the compatibility functions list), to allow compatibility with previous versions of Excel.

However, the function Confidence may not be available in future versions of Excel, so we recommend using the feature Confidence.Norm, if possible.

The function Confidence Excel uses a normal distribution to calculate a confidence value that can be used to construct the confidence interval for a population mean, a given probability, and a sample size. It is assumed that the population standard deviation is known.

Syntax

= CONFIDENCE( alpha, standard_dev, size )

Topics

alfa: The significance level (= 1 – confidence level). (For example, a significance level of 0,05 equates to a 95% confidence level).
standard_dev: The population standard deviation.
size: The size of the population sample.

To calculate the confidence interval for a population mean, the returned confidence value must then be added to and subtracted from the sample mean. Meaning what. for the sample mean x:

Confidence Interval = x ± CONFIDENCE

Example

In the spreadsheet below, the Excel confidence function is used to calculate the confidence interval with a significance of 0,05 (i.e. a 95% confidence level), for the mean of a sample of 100 men's heights . The sample mean is 1,8 meters and the standard deviation is 0,07 meters.

The previous function returns a confidence value of 0,013719748

Therefore the confidence interval is 1,8 ± 0,013719748, which is equivalent to the range between 1,786280252 and 1,813719748

`CONFIDENCE.NORM`

In statistics, the confidence interval is the range within which a population parameter is likely to fall, for a given probability.

For example. For a given population and a 95% probability, the confidence interval is the range in which a population parameter is 95% likely to fall.

Note that the accuracy of the confidence interval depends on whether the population has a normal distribution.

The function Confidence.Norm Excel uses a normal distribution to calculate a confidence value that can be used to construct the confidence interval for a population mean, a given probability, and a sample size. It is assumed that the population standard deviation is known.

Syntax

= CONFIDENCE.NORM( alpha, standard_dev, size )

Topics

alfa: The significance level (= 1 – confidence level). (For example, a significance level of 0,05 equates to a 95% confidence level).
standard_dev: The population standard deviation.
size: The size of the population sample.

To calculate the confidence interval for a population mean, the returned confidence value must then be added to and subtracted from the sample mean. Meaning what. for the sample mean x:

Confidence Interval = x ± CONFIDENCE

Example