Statistical analysis of wages

In one of the earlier „recipes“ I briefly described the statistical functions in Excel. My knowledge of statistics is quite modest (one of these days will probably take some additional lessons to improve my knowledge). However, I would like to dedicate one article to this area. On a practical case, the analysis of wages in a small company, I’ll show you how you can use these functions to make you come up with some interesting conclusions.

For starters, you need to create a table with a list of employees and amount of their salary in €. How to calculate the average wage? This is simply using the AVERAGE. For a given data should enter the formula:

=AVERAGE(C2:C9)

And as a result, we get the amount of € 622.22. Similarly, writing a formula in which we AVERAGE to replace with MIN or MAX, we calculate the minimum and maximum salary.

The average salary is useful information, but since there is a large range between maximum and minimum salary this a bit like the joke in which “some eat the cabbage, some eat meat, but on average we eat cabbage rolls.” It is necessary to calculate the “middle” salary which we get by using median- value of statistical collection is divided into two equal parts. Let’s create a formula:

=MEDIAN(C2:C10)

The result is € 450. For further analysis, we can use the function to calculate percentiles. Percentile is a statistical measure that shows value below which a given percentage of observations in a group of observations fall. For example, if you enter a formula:

=PERCENTILE(C2:C10,0.75)

result is that the value of salary that has less than 75% of employees. That is, 25% of employees have calculated or a higher salary. Next we can “play” with percentiles to get the answers for all „questions“ we’re interested in. Or, we can use Box&Whisker chart for further analysis. Newer versions of Excel have the same functions and syntax, PERCENTILE.INC and PERCENTILE.EXC, that enable computing percentile with more or less accuracy.

Furthermore, it is convenient to measure how much wages vary in relation to the average. For this purpose, we calculate the standard deviation and variance with the help function STDEV and VAR. Both of these functions have two versions. The first ends with P (STDEV.P, VAR.P) and it is used when we know all the elements of a dataset, as in our case. If we had data about nine randomly selected workers, a sample, we would use functions ending with S (STDEV.S, VAR.S).