What is the MEDIAN Function?
The MEDIAN Function is categorized under Statistical functionsFunctionsThe Median Function is categorized under Statistical functions. The function will calculate the middle value of a given set of numbers. Median can be. The function will calculate the middle value of a given set of numbers. Median can be defined as the middle number of a group of numbers; that is, half the numbers return values that are greater than the median, and half the numbers return values that are less than the median. For example, the median of 2, 3, 3, 5, 7, and 10 is 4.
In financial analysisFinancial Analyst Job DescriptionSee a real Financial Analyst Job Description including all the skills, experience, and education required to be the successful candidate for the job. Perform financial forecasting, reporting, and operational metrics tracking, analyze financial data, create financial models, the function can be useful in calculating the median of certain numbers, i.e. the median sales or median expenses.
Formula
=MEDIAN(number1, [number2], …)
The function uses the following arguments:
- Number1 (required argument) – The number arguments are a set of one or more numeric values (or arrays of numeric values), for which we wish to calculate the median.
- Number2 (optional argument)
For the MEDIAN function, remember that:
- If there is an even number of values in the dataset, the average of the two middle values is returned.
- If a supplied array contains empty cells, text, or logical values, these values are ignored by the function when calculating the median.
- In current versions of Excel (Excel 2007 and later), you can provide up to 255 number arguments to the MEDIAN function. In Excel 2003, the function can only accept up to 30 number arguments. However, each of the number arguments can be an array of many values.
How to use the MEDIAN Function in Excel?
As a worksheet function, MEDIAN can be entered as part of a formula in a cell of a worksheet. To understand the uses of the function, let us consider a few examples:
Example 1
Let’s understand how this function would calculate the median using the set of values below:
Here we provided an odd set of values. The formula used was:
We get the results below:
Suppose we now provide an even set of numbers and use the formula below:
As it is an even set of numbers, the average of the two middle values is returned.
Example 2
For the data below:
We used the following formula:
While calculating the median for the above range, the function ignored the logical values and gave the result below:
Click here to download the sample Excel file
Additional resources
Thanks for reading CFI’s guide to important Excel functions! CFI is the official provider of the Financial Modeling & Valuation Analyst certificationFMVA™ CertificationThe Financial Modeling & Valueation Analyst (FMVA)™ accreditation is a global standard for financial analysts that covers finance, accounting, financial modeling, valuation, budgeting, forecasting, presentations, and strategy..
By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:
- Excel Functions for FinanceExcel for FinanceThis Excel for Finance guide will teach the top 10 formulas and functions you must know to be a great financial analyst in Excel. This guide has examples, screenshots and step by step instructions. In the end, download the free Excel template that includes all the finance functions covered in the tutorial
- Advanced Excel Formulas Course
- Advanced Excel Formulas You Must KnowAdvanced Excel Formulas Must KnowThese advanced Excel formulas are critical to know and will take your financial analysis skills to the next level. Advanced Excel functions you must know. Learn the top 10 Excel formulas every world-class financial analyst uses on a regular basis. These skills will improve your spreadsheet work in any career
- Excel Shortcuts for PC and MacExcel Shortcuts PC MacExcel Shortcuts - List of the most important & common MS Excel shortcuts for PC & Mac users, finance, accounting professions. Keyboard shortcuts speed up your modeling skills and save time. Learn editing, formatting, navigation, ribbon, paste special, data manipulation, formula and cell editing, and other shortucts