**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.