Why do politicians lie to us when talking about average wages? Let’s ask Excel.
Let’s calculate some average. The AVERAGE function is a simple function that sums up values from all cells in a range and divides it by the number of cells with values (Fig. 1).
=AVERAGE(D2:D12)
In our example, the average is $2,900. It means that 8 people earn less, which gives us around 80% of all people. It’s important that one person at the bottom earns much more than the rest and affects our average more than other people (Fig. 2).
If we want to talk more precisely about the distribution of those wages, we can use the MEDIAN function which returns the value in the middle (Fig. 3).
=MEDIAN (D2:D12)
Since our values are sorted, we can clearly see the middle value, which is $2,000. We can see that 50% of people earn this value or less and 50% of people earn this value or more (Fig. 4).
If we have an even number of values and there are two middle values, the MEDIAN function calculates the average of those two values near the middle line (Fig. 5).
=MEDIAN(G2:G11)
We can see that it calculated the middle value (Fig. 6).
If we add one zero to cell D12 which belongs only to one person, we can see that the average hit $11,900. It means that only one person earns more that the average. On the other hand, the median value stayed the same. It’s very important to know this difference (Fig. 7).
I also want to add one column with the RAND function (Fig. 8).
=RAND()
I want to show you that we don’t have to have our data sorted to use the MEDIAN function (Fig. 9).
