The Excel median function calculates the middle number (median) in an existing group of numbers. In contrast to the mean value, the median does not give you the average, but the central tendency of a number series ordered by size. Learn here how to use the median function in Excel.
In statistics, the median is a special threshold value known as the quantile, which divides a given series of numbers into two halves. The numbers or values are measured in ascending order. The median shows the numerical value in the middle position of the examined series of numbers.
The median is a practical Excel function to obtain concrete central values. To determine the median in Excel, use the following formula syntax:
=MEDIAN(number1;number2;…)The formula syntax of the Excel median function consists of the necessary, numeric variable “Number1” and optional, subsequent variables that specify from which number series the center is to be calculated. The variables “Number1”, “Number22 etc. can be numbers as well as cell references.
If you want to insert the median of a group of numbers in Excel, double-click in a cell and enter “=MEDIAN(A1:E1)”, for example. This will give you the median number of the numerical values in cells A1 to E1. Alternatively, you can integrate the median formula directly in a tabular overview as a column or in a row.
Example:
The median function is able to calculate the mean value from 1 to 255 (maximum column width in Excel). However, you need to ensure that the arguments are numeric values, i.e. ordinally scaled variables. The function ignores text, names, matrices, references, and logical values that do not contain numbers. The median of TRUE/FALSE values and formulated numbers, on the other hand, can be determined by the function.
The median reflects the central tendency of ascending numerical values in their statistical distribution. To determine a central tendency, you have three options:
If list values are evenly distributed, the median may resemble the mean or modal value. If the values are unevenly distributed, the median usually deviates from the mean and modal value.
The number of values must be distinguished between an even and odd number of values:
The median is used when the central value of data with ordinal, interval, or ratio scaling is to be calculated. Common areas of application are:
To understand the practical utility of the median function, consider the following practical examples of use:
The central value of annual income among a group of eight persons is to be determined. Depending on the distribution of income sizes, the mean may determine the average income, but it may not give a realistic picture if 80 percent earn below a certain threshold and 20 percent earn above it. This is where the mean comes into play.
| A | B |
1 | Person | Annual income in $ |
2 | Silvia | 44,000 |
3 | Peter | 500,000 |
4 | Karim | 31,000 |
5 | Azra | 33,000 |
6 | Lin | 75,000 |
7 | Markus | 900,000 |
8 | Claudia | 25,000 |
Median function | =MEDIAN(B2:B8) | 44,000 |
The median reflects the middle of the income ratios, i.e. the annual income of the “average” employee. The mean value, on the other hand, would be $229,714 – the average annual income in this case, which can be understood as a distorted income ratio.
In the case of an even number of eight people, the median is determined from the average of the lower and upper median in each case (here: B3 and B6):
| A | B |
1 | Person | Annual income in $ |
2 | Matthias | 10,000 |
3 | Silvia | 44,000 |
4 | Peter | 500,000 |
5 | Karim | 31,000 |
6 | Azra | 33,000 |
7 | Lin | 75,000 |
8 | Markus | 900,000 |
9 | Claudia | 25,000 |
Median function | =MEDIAN(B2:B9) | 38,500 |
When determining the tendency of school grades, the median helps calculate the middle of a group of grades ordered by size:
| A | B |
1 | Student | Grade |
2 | Klaus | 3 |
3 | Emma | 1 |
4 | Egane | 2 |
5 | Abebi | 3 |
6 | Kenan | 1 |
7 | Anna | 5 |
8 | Yasi | 4 |
Median function | =MEDIAN(B2:B8) | 3 |
The median can be applied without Excel, of course. Here, the numbers of a group have to be arranged in ascending order and the median number (in the case of an odd number) or the average of the two median numbers (in the case of an even number) has to be determined.
It can often be incredibly time-consuming to search for a specific entry in an Excel table by hand, which is where VLOOKUP comes into play. This practical function allows you to find the exact value for a specific search criterion. The VLOOKUP function is indispensable for managing price lists, members directories, and inventory catalogues. To ensure you can benefit from this practical function,...
When working with big datasets, it’s easy to lose sight of the bigger picture. If you want to add row numbering in Excel, there’s no convenient button to do so. Find out how you can easily integrate continuous numbering in Excel and adjust it to your needs.
Want to know how to calculate percentages in Excel? With the most important formulas for calculating percentages in Excel, the process is a cinch. In this article, you’ll learn how to calculate percentages of totals and growth rates, and how to figure out proportions and totals based on percentages. We’ll also give you tips on increasing and decreasing numbers by percentages.
Provide powerful and reliable service to your clients with a web hosting package from IONOS.
View packages