Measures of location

Mode

The most common value for a variable based on its frequency, can be calculated from either qualitative or quantitative data. The MODE function can be used in Excel to return this.

 

 

Mean

The average value based on a variable of quantitative data.

 

The arithmetic mean is the most commonly used. In Excel, the AVERAGE function returns the arithmetic mean which simply divides the total of all values by the number of observations.

 

The geometric mean is returned by the GEOMEAN function, which is often used to measure the average rate of growth e.g. when calculating interest rates over a number of years.

 

The harmonic mean is calculated using the HARMEAN function in Excel. One potential use would be calculating the average rate of speed e.g. if you travel 10 miles at 30 miles per hour and then another 10 miles at 70 miles per hour the formula =HARMEAN(30,70) will return the result of 42.

 

 

Median

The central value of a variable of quantitative data. Using the median instead of the mean lessens the impact of outliers.

Example: The median UK salary in 2017 was around  £22,000 whereas the mean was closer to £26,500 and more heavily influenced by some of be large outliers from the top earners.

 

 

Range

The difference between the maximum and minimum values of a quantitative variable in a data set.

 

 

Midrange

Found by taking the average of the minimum and maximum values from the data set.

 

 

Outlier

A unit that falls far from the rest of the data, which can have a misleading impact on the mean. Outliers can be measured in a couple of different ways, simply as any observations outside of 1.5 multiplied by the IQR (interquartile range) or alternatively outliers can be deemed as any observations outside of two standard deviations from the mean.

 

The TRIMMEAN function in Excel can be used to exclude outliers from your measure of central tendency. You can use TRIMMEAN to specify a percentage of the top and bottom values to exclude from the mean calculation.

 

 

Measures of location and spread

The mean and the median are measures of location. The standard deviation and interquartile range are measures of spread.

 

The mean and standard deviation are commonly used together, these are less resistant measures which are sensitive to outliers. The median and interquartile range are commonly used together, they are more resistant measures not impacted by outliers.