Average, median, percentiles and modal values

Introduction

Average, median, percentiles and mode are four ways of looking at a set of numbers, that are used quite a lot and quite simple to use.

Average

If you have a set of numbers, it might be interesting to find the average number. This is done in Excel and Calc using AVERAGE(). Let's say we need to calculate the average for the content in cells C2 to C6 in cell C7, the syntax in cell C7 is =AVERAGE(C2:C6).

For Microsoft Excel it looks like this:

For OpenOffice Calc it looks like this:

Median

If you have a set of numbers, it might be interesting to find the median number. The median is a middle number in a set of numbers when ordered from smallest to largest number, thus reflecting a tendency in the numbers that can't be seen when using average. This is done in Excel and Calc using MEDIAN(). Let's say we need to calculate the median for the content in cells C2 to C6 in cell C7, the syntax in cell C7 is =MEDIAN(C2:C6).

For Microsoft Excel it looks like this:

For OpenOffice Calc it looks like this:

Percentiles

Instead of the median, which is the 50th percentile, you might be interested in finding the 1st quartile (25th percentile) or 3rd quartile (75th percentile) or some other percentile of interest for the user. In Excel and Calc this is done using PERCENTILE(). The function requires a specification of the cells containing the numbers and a specification of the percentile, separated by semicolon. Let's say we want to calculate the 25th percentile of the content in cells C2 to C6 in cell C7, then in cell C7 you write =PERCENTILE(C2:C6;0,25).

For Microsoft Excel it looks like this:

For OpenOffice Calc it looks like this:

Mode

Instead of the average or median, it might be of interest to find the mode, also called the modal value, which is the number appearing the most times. This is done in Calc and older versions of Excel (before 2010) using MODE(). MODE() also works in Excel versions from 2010 and later, but here you should use the updated commands shown below. Let's say you need to find the mode for the content in cells C2 to C6 in cell C7, then in cell C7 you write =MODE(C2:C6).

For Microsoft Excel it looks like this:

For OpenOffice Calc it looks like this:

If you use the function MODE(), you have to be aware of two things:
1. Finding the mode requires that one of the values appear more than once. If no value appears more than once, an error message will appear.
2. Having multiple modes in a set of numbers, due to multiple values appearing the same number of times, is not a problem, and here you have to be aware that there is a difference between Calc og Excel. In Calc, MODE() only show the lowest modal value, whereas Excel only show the first of the modal values it finds.

Because there can be multiple values appearing the most times, MODE() has been split up in MODE.SNGL() and MODE.MULT() in the newer versions of Excel. MODE.SNGL() and MODE.MULT() are only supported by the file format .xlsx, and can't be used in the old Excel format .xls. MODE.SNGL() is for showing only one modal number, and works just like MODE(). MODE.MULT() is a bit more tricky!

Let's say that once again we want to find the modal number, or numbers if there is more than one, for the content in cells C2 to C6. If there is more than one mode, the numbers have to be places in separate cells, so cell C7 is not enough. Here we select three cells, C7 to C9. To make it work, you do it like this:
1. Select the three cells.
2. In the formula field write =MODE.MULT(C2:C6). (Do NOT press ENTER here!)
3. Press CTRL + SHIFT + ENTER. Now you can see that in all three cells it says {=MODE.MULT(C2:C6)}.

In the spreadsheet, it looks like this:

It is of no use writing {=MODE.MULT(C2:C6)} in the three cells, as you would normally do. It does not work.

If there is only one mode, Excel will write the same number in all cells. If there is more than one, they are listed in order of first appearance in the cells. If you have more fields for modal numbers than needed, a #N/A (Not Applicable) will appear in the extra cells. If you are working with large sets of number, it is a good idea to select a number of cell for modal values exceeding the number of modal values, so the appearance of at least one #N/A (or all cells displaying the same value) works as an insurance that you aren't missing any modal values on the list.

Do be aware that MODE.SNGL() and MODE.MULT() only works for data arranged in columns. If you need to find modal values in a row, you need to add TRANSPOSE(), i.e. it needs to say TRANSPOSE(MODE.SNGL()) and TRANSPOSE(MODE.MULT()) in the cells.

Excel function names in various languages

For some reason Microsoft has decided to have localized function names, so for non-English versions of Excel, you need to use the translated function names. This is as far as I have gotten, finding the localized function names for this chapter:

 Catalan MITJANA() Czech PRŮMĚR() Danish MIDDEL() Dutch GEMIDDELDE() English AVERAGE() Finnish KESKIARVO() French MOYENNE() Galician MEDIA() German MITTELWERT() Hungarian ÁTLAG() Italian MEDIA() Norwegian GJENNOMSNITT() Polish ŚREDNIA() Portuguese (BR) MÉDIA() Portuguese (PT) MÉDIA() Russian СРЗНАЧ() Spanish PROMEDIO() Swedish MEDEL() Turkish ORTALAMA()

 Catalan MEDIANA() Czech MEDIAN() Danish MEDIAN() Dutch MEDIAAN() English MEDIAN() Finnish MEDIAANI() French MEDIANE() Galician MEDIANA() German MEDIAN() Hungarian MEDIÁN() Italian MEDIANA() Norwegian MEDIAN() Polish MEDIANA() Portuguese (BR) MED() Portuguese (PT) MED() Russian МЕДИАНА() Spanish MEDIANA() Swedish MEDIAN() Turkish ORTANCA()

 Catalan PERCENTIL() Czech PERCENTIL() Danish FRAKTIL() Dutch PERCENTIEL() English PERCENTILE() Finnish PROSENTTIPISTE() French CENTILE() Galician PERCENTIL() German QUANTIL() Hungarian PERCENTILIS() Italian PERCENTILE() Norwegian PERSENTIL() Polish PERCENTYL() Portuguese (BR) PERCENTIL() Portuguese (PT) PERCENTIL() Russian ПЕРСЕНТИЛЬ() Spanish PERCENTIL() Swedish PERCENTIL() Turkish YÜZDEBİRLİK()

 Catalan MODA() Czech MODE() Danish HYPPIGST() Dutch MODUS() English MODE() Finnish MOODI() French MODE() Galician MODO() German MODALWERT() Hungarian MÓDUSZ() Italian MODA() Norwegian MODUS() Polish WYST.NAJCZĘŚCIEJ() Portuguese (BR) MODO() Portuguese (PT) MODA() Russian МОДА() Spanish MODA() Swedish TYPVÄRDE() Turkish ENÇOK_OLAN()

 Catalan TRANSPOSA() Czech TRANSPOZICE() Danish TRANSPONER() Dutch TRANSPONEREN() English TRANSPOSE() Finnish TRANSPONOI() French TRANSPOSE() Galician TRASPOR() German MTRANS() Hungarian TRANSZPONÁLÁS() Italian MATR.TRASPOSTA() Norwegian TRANSPONER() Polish TRANSPONUJ() Portuguese (BR) TRANSPOR() Portuguese (PT) TRANSPOR() Russian ТРАНСП() Spanish TRANSPONER() Swedish TRANSPONERA() Turkish DEVRİK_DÖNÜŞÜM()