Let’s start with what the average means. In terms of descriptive statistics, the average is a certain reference (typical) value. It is a property of the data set that we are investigating. It belongs to the so-called measures of central tendency. If we have a large amount of data, we can describe the data with some measures of central tendency – this includes different types of means, median, mode, etc. In this article, we will focus on the mean and its types.
Types of Means (Average) and Excel Functions
6 basic types of means describe the data:
- Arithmetic mean
- Weighted arithmetic mean
- Moving average
- Geometric mean
- Harmonic mean
- Quadratic mean
Arithmetic Mean – Excel Function AVERAGE()
The arithmetic mean is the simplest type. It is most often found in various calculations of the general population. However, arithmetic mean has a significant disadvantage. Probably every one of us has ever heard of some mean wage statistics.
The arithmetic mean wage does not correctly describe the data sample – ie wages of the general population, because in real terms there are large inequalities in wages.
In Excel, the arithmetic mean is calculated using the function =AVERAGE. In the example below, we have 5 employees of which the last 2 have very high wages.
We can see that the arithmetic mean in this case completely misrepresents the sampling data and it is better to describe the data using the median. However, in the case of small differences in wages, the arithmetic mean and median would be similar.
Weighted Arithmetic Mean SUMPRODUCT () / SUM ()
The weighted arithmetic mean is a far more useful descriptive characteristic than just Arithmetic mean. If we have some data of different importance, we can use this type of mean to take into account the importance of individual values. A mathematical formula can be found on Wikipedia.
A typical example of this is when we have a group of several loans with different interest rates. Let’s say we want to calculate the average interest rate.
By arithmetic mean we get value for interest rate 16%. This is wrong, because loans with the highest rates have a low principal (low weight). If we want to consider the weight of the loan principal, we have to use the weighted average as in the example.
Geometric Mean – Excel GEOMEAN () Function
The geometric average is the characteristic that is most often used to calculate the average if we have a statistical set with eg growth rates over time (eg GDP growth). It is calculated similarly to the arithmetic mean. Multiplication is used instead of addition, and the square root is used instead of division (arithmetic mean).
Moving Average – Excel Function AVERAGE
The moving average is the average over a given number of previous values. There are several types of which the basic is simple moving average. In addition, there is, for example, an exponential moving average, etc.
These averages are often used to smooth out any volatile data set. The real use can be seen on the EUR / CZK exchange rate for 2019. The moving average is many consecutive ordinary averages calculated using the function = AVERAGE.
For a 7-day moving average, we always take the last 7 values for each row (see figure).
If we show both time series in the chart, we can see that the 7D moving average smoothes the data. If the moving average period is longer (eg 30 days, 3 months, etc.), the curve becomes more flat and smooth.
Frequent use is in the analysis of securities or FX currencies and derivatives. Fans of so-called technical analysis believe that if a stock or some other financial instrument’s rate intersects the long-term moving average (from below or above), it is a signal to buy/sell.
