Contents
To calculate the median in Excel, you can use the built-in MEDIAN function. Let’s try to figure out what the median is and consider methods for calculating it for a sample or distribution of random numbers.
Sample median
So, the median is a number that expresses the middle of a set of a certain series of numbers. Thus, half of the numbers of the specified series will be greater than the value of the calculated median, and the second half of the numerical series will be less. In order to determine the median, you need to sort the numerical values of the sample. As a result, the median of the sample of numbers 2, 2, 3, 5, 6, 7, 8 will be 5. In this case, the sample consists of seven values - three digits more than the resulting median and three less.
Important! If the sample consists of an even number of integer numeric values, then the median will be represented as a decimal fraction. Thus, the median of the sample series 2, 2, 3, 5, 6, 7, 8, 10 is 5,5.
The calculation of the median of the sample through the capabilities of Excel is carried out using the corresponding MEDIAN function. You can put up to 255 values in brackets of the formula, from which you need to calculate the median.
The median does not always coincide with the average numerical value from the presented series. As a rule, coincidence occurs only with symmetrical identity relative to the average.
Pay attention! The median is not the same as the mean. The mean of the sample of numbers 2, 2, 3, 5, 6, 7, 300 corresponds to the value 46,42857, while the median is still 5, since the ratio of numbers greater than 5 and less has not changed.
Median of a continuous distribution
Now consider the calculation of the median in the case of a continuous distribution. The median index is the result of solving the distribution function of random continuous numerical values. If the distribution function exponent (i.e. the density function) is known, then the median value can be calculated using the following formula:
When solving this equation by an analytical method with a log-normal distribution LnN (µ; σ; 2), it is fashionable to calculate the median value thanks to the formula =EXP (µ). Provided that µ=0, the median will be equal to 1. In Excel, the result of the median for the lognormal distribution, represented by the formula LnN (0; 1) is calculated through the function =LOGNORM.ODR (0,5; 0; 1).
MEDIAN function
Using the MEDIAN function in Excel, you can calculate the value of the middle of a number set. The syntactic expression is a formula u1d MEDIAN (number 2; [number XNUMX] …). The arguments are “Number 1”, “Number 2”, and so on up to “Number 255”. The first value is a mandatory value, the subsequent ones are optional, but they help to calculate the median.
It should be noted! As arguments, not only numeric values can be used, but also links, names, arrays. If the link or array is partially represented as text or there are empty cells in the selection, then these values are not taken into account in the calculation process. The use of text format in arguments leads to errors in the function.
Average value of a series of numbers
Consider the option of calculating the average values of a certain series of numbers. For example, it can be the average air temperature on a particular day of the year, the performance of tasks by employees, and so on. To perform this calculation, three important parameters are required:
- Median – the average value of the numerical series. At the same time, half of the numbers are greater than the median, and the other half are less. For example, the median of the number series 2, 2, 3, 5, 6, 7, 9 is 5.
- Secondary value – this is the arithmetic average, which is calculated by summing all the numbers of a certain series and dividing this sum by the number of indicators, that is, 2 + 2 + 3 + 5 + 6 + 7 + 9 u34d 7/4,857143. In this case, the average value corresponds to the indicator XNUMX.
- Index Mode – this is the selection of a number that occurs more often than others in a number series. In the presented series of numbers 2, 2, 3, 5, 6, 7, 9, this indicator corresponds to the number 2.
The indicated parameters do not change their values with a symmetrical distribution of the numerical series, and they can change with an asymmetric distribution.
Computing Continuous Series Values
When determining the average value of a continuous numerical series, it is necessary to carry out a certain procedure:
- You must activate the cell to the right or below the row or row that contains the values.
- On the “Main” tab, select the “AutoSum” option, which can be found in the “Editing” tool group.
- Also, this function can be found in the “Formulas” tab.
- Next to the “AutoSum” button, which is indicated by the Σ icon, there is an arrow with a built-in menu. Select “Average”. This value can also be obtained by executing the AVERAGE function.
Weighted average
In this case, you will have to use several functions – SUM and SUMPRODUCT. Let’s try to calculate the average cost of a unit of goods in the presence of three goods and the number of sales made. The formula syntax looks like this: =SUMPRODUCT(R[-3]C : R[-3]C[1] : R[-1]C[1]) / SUM(R[-3]C[1] : R[-1]C[1]). After executing this function, we get the average value of the cost of one unit of goods 184,5238095
Nullless Calculation
In order to ignore zero values in the calculation process, it is necessary to use two functions – IF and AVERAGE. Consider an example. In the presented number series 4, 6, 8, 0, 5, 8, it is necessary to find the average value, but with the condition of excluding the zero value. The function will have the following syntax: =AVERAGEIF(A2:A7, “<>0”). As a result, the result of the function without zero will be equal to 6,2.
Useful videos
You can get acquainted with the basic principles of calculating the median in Excel by watching several tutorial videos on YouTube