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:
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:
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:
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:
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:
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.
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:
- 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.
- 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:
- Select the three cells.
- In the formula field write =MODE.MULT(C2:C6). (Do NOT press ENTER here!)
- 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() |