Microsoft Excel has been one of Microsoft’s iconic releases and even many years have passed since it first came into being but its popularity has only increased. Excel provides us with the power of tabulating and evaluating data of all sorts and types with main focus given to the operation asked for by the business user. Not only is it simple to use and easy on the eye but it boasts of a huge number of functions that are tailored for any need of any user, no matter how robust and huge the task is or even how trifle it is. You do not need to be an expert Excel consultant or advanced business user to use Excel; anyone can use Excel. And with our free Excel help articles you can learn how to use all of the important Microsoft Excel functions. One such function is the MEDIAN function.
There are a few measures of the central tendency to a set of numbers or readings that are very essential for an analysts or accountant. Such measures are usually arithmetic mean, median and mode. One such central tendency measuring function is the MEDIAN function and it calculates the median of the numbers that are provided by the user to it. So if the Excel consultant mentions the elements of one or more arrays then, the median among all those array elements gives the result of the MEDIAN function. The function is usually written as MEDIAN (value1, value2…valuen). Each of these values inserted by the business user is taken up and provides input for the function to compute on. The median calculation does have different procedures though depending upon the number of elements inserted. If the number of elements inserted is odd, then straightaway we can say that the element in the middle position is the median. For example if there are 11 elements, then the 6th element is the median but for even number of elements the middle two elements are summed up and their average actually gives the median. The MEDIAN function can take up a maximum of 30 such elements as input.
For example, if the consultant gives the following input MEDIAN (13, 45, 23, 21, 31) then the median is computed to be 23 as there are 5 elements (odd) and the middle element is the 3rd one and here it comes to be 23. But if we add a 34 in the end i.e. the function now looks like MEDIAN (13, 45, 23, 21, 31, 34) and so it has two middle elements now, namely the 3rd and the 4th and their average comes out to be (23+21)/2 = 22 and so 22 is the median value here. Even array sizes can be passed on as well through the MEDIAN function like MEDIAN (A2:A7) etc. So, it is evident that the function is very easy to understand and use.
Some of the features of the MEDIAN function are:
MEDIAN can take up cells or arrays that have absolutely numerical values in them and no text representations at all. Otherwise it just overlooks the abnormal parts in the function and computes for the remaining numbers that are inserted.
Cells which have only numerical values are included in the function. But if the cells do contain any non-numerical value like textual symbols, logical values etc. they are not at all taken into account by the MEDIAN function and are thus overlooked. If empty cells are there then they are also overlooked but if any cell contains a 0, then it is included and 0 is taken as its value.
In order to pass any textual representation of a number or any logical value, it is better to write them explicitly in the argument section of the function as then the function translates them into their proper numerical counterpart and computes on them also. So, MEDIAN (…”SIX”) and MEDIAN (…TRUE) are both permissible and accounted for.
The MEDIAN function however cannot account for any text that doesn’t translate into a number and those parameters are overlooked by the function or they cause errors. Even error values if passed through the function are not at all identified by it and so they also show error signs.
The MEDIAN function is found in almost all the popular descendents of the Excel namely Excel 2000, 2003, 2007, 2008, 2010 and 2011. It is very popular among accountants and Excel consultants as well as business users, while finding central tendencies as it calculates the median among a set of numbers very efficiently indeed.