MEDIAN Function in Microsoft Excel
Part 1: Introduction
Definition
The MEDIAN function in Microsoft Excel is a statistical function that returns the median (the middle number) of a given set of numbers.
Purpose
The purpose of the MEDIAN function is to measure central tendency, which is the location of the center of a group of numbers in a statistical distribution. It’s beneficial when you want to find the middle value in a dataset, which can be more representative of the dataset than the average in the presence of outliers.
Syntax & Arguments
The syntax of the MEDIAN function is as follows:
MEDIAN(number1, [number2], ...) Explanation of Arguments
- Number1, number2, …: Number1 is required. Subsequent numbers are optional. These are the 1 to 255 numbers for which you want the median.
Return Value
The MEDIAN function returns the median of the given numbers. If there is an even number of numbers in the set, then MEDIAN calculates the average of the two numbers in the middle.
Remarks
- Arguments can be numbers or names, arrays, or references containing numbers.
- Logical values and text representations of numbers you type directly into the list of arguments are counted.
- If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with zero values are included.
- Arguments that are error values or text that cannot be translated into numbers cause errors.
Part 2: Examples
Example 1
Purpose of Example: To find the median sales value for a small business.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Sales | Formula |
| 2 | $200 | |
| 3 | $300 | |
| 4 | $250 | |
| 5 | Result | =MEDIAN(A2:A4) |
| 6 | $250 (Result) |
Explanation: In this example, we have three sales values: $200, $300, and $250. The median sales value is $250; the middle value is when the numbers are arranged in ascending order.
Example 2
Purpose of Example: Find the median number of units a company sells.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Units Sold | Formula |
| 2 | 50 | |
| 3 | 75 | |
| 4 | 100 | |
| 5 | Result | =MEDIAN(A2:A4) |
| 6 | 75 (Result) |
Explanation: In this example, we have three values for units sold: 50, 75, and 100. The median number of units sold is 75, the middle value when the numbers are arranged in ascending order.
Example 3
Purpose of Example: To find the median number of employees in different company branches.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Employees | Formula |
| 2 | 30 | |
| 3 | 45 | |
| 4 | 35 | |
| 5 | Result | =MEDIAN(A2:A4) |
| 6 | 35 (Result) |
Explanation: In this example, we have three values for the number of employees: 30, 45, and 35. The median number of employees is 35, the middle value when the numbers are arranged in ascending order.
Example 4
Purpose of Example: To find the median cost of goods a company sells.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Cost | Formula |
| 2 | $500 | |
| 3 | $600 | |
| 4 | $550 | |
| 5 | Result | =MEDIAN(A2:A4) |
| 6 | $550 (Result) |
Explanation: In this example, we have three values for the cost of goods sold: $500, $600, and $550. The median price is $550, the middle value when the numbers are arranged in ascending order.
Example 5
Purpose of Example: To find the median number of hours employees work in a company.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Hours | Formula |
| 2 | 8 | |
| 3 | 9 | |
| 4 | 8.5 | |
| 5 | Result | =MEDIAN(A2:A4) |
| 6 | 8.5 (Result) |
Explanation: In this example, we have three values for the number of hours worked: 8, 9, and 8.5. The median number of hours worked is 8.5, the middle value when the numbers are arranged in ascending order.
Example 6: Using MEDIAN with IF
Purpose of Example: To find the median salary of employees in a specific department.
Data Tables and Formulas:
A B C D 1 Employee Salary Department Median 2 John $40,000 HR 3 Sarah $55,000 Sales 4 Mike $50,000 HR 5 =MEDIAN(IF(C2:C4=”HR”,B2:B4)) $45,000 Explanation: This example calculates the median salary for the HR department. Using the IF function within MEDIAN, we can filter the wages based on the department. The median salary for the HR department is $45,000.
Example 7: Using MEDIAN with SUM
Purpose of Example: To find the median of the total sales for three products over three months.
Data Tables and Formulas:
A B C D E 1 Product Jan Feb Mar Median 2 A 100 150 200 3 B 150 200 250 4 C 200 250 300 5 =MEDIAN(SUM(B2:D2), SUM(B3:D3), SUM(B4:D4)) 600 Explanation: This example calculates the median total sales for three products over three months. Using the SUM function within MEDIAN, we can sum the sales for each product and then find the median of these sums. The median total sales value is 600.
Example 8: Using MEDIAN with VLOOKUP
Purpose of Example: To find the median price of a specific category of products.
Data Tables and Formulas:
A B C D 1 Product Price Category Median 2 P1 $10 A 3 P2 $20 B 4 P3 $15 A 5 P4 $25 B 6 =MEDIAN(VLOOKUP(“A”, A2:C5, 2, FALSE), VLOOKUP(“B”, A2:C5, 2, FALSE)) $17.50 Explanation: This example calculates the median price for products in categories A and B. Using the VLOOKUP function within MEDIAN, we can find the price for each type and then calculate the median. The median price for these categories is $17.50.
Example 9: Using MEDIAN with AVERAGE
Purpose of Example: To find the median of students’ average scores in three subjects.
Data Tables and Formulas:
A B C D E 1 Student Math Sci Eng Median 2 Alice 80 70 60 3 Bob 70 60 50 4 Charlie 60 50 40 5 =MEDIAN(AVERAGE(B2:D2), AVERAGE(B3:D3), AVERAGE(B4:D4)) 60 Explanation: This example calculates the median average scores for three students across three subjects. Using the AVERAGE function within MEDIAN, we can find the average score for each student and then calculate the median. The median average score is 60.
Example 10: Using MEDIAN with MAX
Purpose of Example: To find the median maximum temperatures for three cities.
Data Tables and Formulas:
A B C D E 1 City Jan Feb Mar Median 2 City A 30 32 28 3 City B 28 30 29 4 City C 29 31 30 5 =MEDIAN(MAX(B2:D2), MAX(B3:D3), MAX(B4:D4)) 31 Explanation: This example calculates the median maximum temperatures for three cities over three months. Using the MAX function within MEDIAN, we can find the maximum temperature for each city and then calculate the median. The median maximum temperature is 31.
Example 11: Using MEDIAN with MIN
Purpose of Example: To find the median of the minimum sales for three products.
Data Tables and Formulas:
A B C D E 1 Product Jan Feb Mar Median 2 A 100 150 200 3 B 150 200 250 4 C 200 250 300 5 =MEDIAN(MIN(B2:D2), MIN(B3:D3), MIN(B4:D4)) 150 Explanation: This example calculates the median of the minimum sales for three products over three months. Using the MIN function within MEDIAN, we can find the minimum deals for each product and then calculate the median. The median, minimum sales value is 150.
Example 12: Using MEDIAN with COUNT
Purpose of Example: Find the median sales counts above a certain threshold for three products.
Data Tables and Formulas:
A B C D E 1 Product Jan Feb Mar Median 2 A 100 200 300 3 B 200 300 400 4 C 300 400 500 5 =MEDIAN(COUNTIF(B2:D2,”>200″), COUNTIF(B3:D3,”>200″), COUNTIF(B4:D4,”>200″)) 2 Explanation: This example calculates the sales median above 200 for three products over three months. Using the COUNTIF function within MEDIAN, we can count the sales above the threshold for each product and then calculate the median. The median count is 2.
Example 13: Using MEDIAN with ROUND
Purpose of Example: Find the median of rounded average temperatures for three cities.
Data Tables and Formulas:
A B C D E 1 City Jan Feb Mar Median 2 City A 30.5 32.3 28.7 3 City B 28.2 30.4 29.6 4 City C 29.3 31.5 30.2 5 =MEDIAN(ROUND(AVERAGE(B2:D2),0), ROUND(AVERAGE(B3:D3),0), ROUND(AVERAGE(B4:D4),0)) 30 Explanation: This example calculates the median of the rounded average temperatures for three cities over three months. Using the ROUND and AVERAGE functions within MEDIAN, we can find the spherical average temperature for each city and then calculate the median. The median rounded average temperature is 30.
Part 3: Tips and Tricks
- The MEDIAN function is unaffected by outliers or skewed data, making it a better measure of the central tendency for skewed distributions.
- You can use the MEDIAN function with logical operators. For example,
MEDIAN(IF(A1:A10>5, A1:A10))it will return the median of the numbers greater than 5 in the range A1:A10. - Remember that the MEDIAN function ignores text, logical values, or empty cells in the array or reference argument. However, cells with the value zero are included.
- If you want to include logical values or text representations of numbers in your calculations, type them directly into the list of arguments.
- Be careful with error values or text that cannot be translated into numbers, as they cause errors.