Statistical functions – Excel kitchenette

Although Excel has fewer features than specialized statistical data processing software like SPSS, it has many statistical functions which make it a powerful tool. You can use them to analyze business reports, and they also apply in all other areas where statistics are used as a data analysis tool.

The AVERAGE function is used to calculate the arithmetic mean of a given set of numbers. Its syntax is:

AVERAGE (<argument₁>,<argument₂>,…,<argument_n>)

Each of the arguments can be the cell address, cell range, name or column. For example, if we enter the formula:

=AVERAGE(10,20,30)

we get the result of an average value for the given arguments, which is number 20.

There is also a variation of this function called AVERAGEA, which calculates the arithmetic mean for arguments that are designed as text values, and when it finds empty cell it assigns value of 0.

To calculate the geometric mean, the GEOMEAN function is used, which has the same syntax as the AVERAGE function:

GEOMEAN (<argument₁>,<argument₂>,…,<argument_n>)

When analyzing statistical sets, it is useful to determine their mode and median. Mode (MODE) represents the central tendency, that is, the number that is most often repeated in a set of given numbers. Median (MEDIAN) represents a value that statistically divides the set into two equal parts. Both the modus and the median have similar syntaxes as a function of AVERAGE, which are:

MODE (<argument₁>,<argument₂>,…,<argument_n>)

MEDIAN (<argument₁>,<argument₂>,…,<argument_n>)

For example, for given set of numbers of 10,10,20,30,50 mode and median are calculated using the following formulas.

=MODE(10,10,20,30,50)

=MEDIAN(10,10,20,30,50)

Resulting mode is 10, and median is 20.

The standard deviation of the members of the set is calculated by standard deviation function (STDEV) or variance function (VARIANCE) which represents the standard deviation per square. The syntax of these functions is:

STDEV (<argument₁>,<argument₂>,…,<argument_n>)

VARIANCE (<argument₁>,<argument₂>,…,<argument_n>)

Another frequently used function is the correlation coefficient (CORREL). This function is used to compare two sets of numbers in order to determine how similar they are. The result is a value between 0 and 1. The value closer to the unit, these are sets more similar. The syntax of this function is:

CORREL (<array₁>,<array₂>)

And if we want to compare, for example, sets of 10,20,30 (A1: A3) and 10,20,20 (B1: B3) we write the formula:

=CORREL (A1:A3,B1:B3)

and get the result 0.87.

The functions shown so far are “the tip of the iceberg”. In following posts will be presented some more of them, as well as charts that can be used for analysis of statistical sets.