# 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:

syntax
```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:

AB
1SalesFormula
2\$200
3\$300
4\$250
5Result=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:

AB
1Units SoldFormula
250
375
4100
5Result=MEDIAN(A2:A4)
675 (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:

AB
1EmployeesFormula
230
345
435
5Result=MEDIAN(A2:A4)
635 (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:

AB
1CostFormula
2\$500
3\$600
4\$550
5Result=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:

AB
1HoursFormula
28
39
48.5
5Result=MEDIAN(A2:A4)
68.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:

ABCD
1EmployeeSalaryDepartmentMedian
2John\$40,000HR
3Sarah\$55,000Sales
4Mike\$50,000HR
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:

ABCDE
1ProductJanFebMarMedian
2A100150200
3B150200250
4C200250300
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:

ABCD
1ProductPriceCategoryMedian
2P1\$10A
3P2\$20B
4P3\$15A
5P4\$25B
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:

ABCDE
1StudentMathSciEngMedian
2Alice807060
3Bob706050
4Charlie605040
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:

ABCDE
1CityJanFebMarMedian
2City A303228
3City B283029
4City C293130
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:

ABCDE
1ProductJanFebMarMedian
2A100150200
3B150200250
4C200250300
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:

ABCDE
1ProductJanFebMarMedian
2A100200300
3B200300400
4C300400500
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:

ABCDE
1CityJanFebMarMedian
2City A30.532.328.7
3City B28.230.429.6
4City C29.331.530.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.