MEDIAN

Statistical Functions

(4.8/5)

Returns the median (middle value) of a set of numbers. The median is the value that separates the higher half from the lower half of a dataset. For odd count, it's the middle value; for even count, it's the average of the two middle values. Essential for robust central tendency analysis, especially when data has outliers.

Interactive Formula Tester

Numbers:

Formula:

=MEDIAN("10, 20, 30, 40, 50")

Complete Theory & Understanding

Master the fundamentals of Excel MEDIAN function

Core Concept

The MEDIAN function returns the median (middle value) of a set of numbers. The median separates the higher half from the lower half of a dataset. For an odd number of values, the median is the middle value when sorted. For an even number, it's the average of the two middle values. MEDIAN is less sensitive to outliers than AVERAGE, making it ideal for skewed distributions. Essential for robust central tendency analysis, handling outliers, statistical analysis, and when data contains extreme values.

Why Use MEDIAN?

Central tendency with outliers
Median in statistics
Median income analysis
Median measurements

Key Characteristics

Middle Value

Value separating higher/lower half

MEDIAN(10,20,30,40,50) = 30

Robust to Outliers

Less affected by extreme values

Better than AVERAGE for skewed data

Even Count Handling

Averages two middle values

MEDIAN(10,20,30,40) = 25

Auto-Sorting

Sorts values internally

Works with unsorted data

Function Anatomy

=MEDIAN(parameters...)

Required

Parameters:

Function-specific parameters

Returns

Return Value:

Function-specific return type

Primary Use Cases

Robust Analysis

Central tendency with outliers

Statistical Analysis

Median in statistics

Income/Salary Data

Median income analysis

Quality Control

Median measurements

Theory Summary

Precise

Exact matching required

Position-Based

Returns numeric position

Error-Safe

Handles missing text gracefully

Syntax & Parameters

=MEDIAN(number1, number2)

Required

number1:

First number, cell reference, or range.

Optional

number2:

Additional numbers, cell references, or ranges (up to 255 arguments).

Returns

Return Value:

The median (middle) value

Description: Returns the median value of a set of numbers

Interactive Examples

Basic MEDIAN (Odd Count)

Median with odd number of values

Input Text:

"10, 20, 30, 40, 50"

Formula:

=MEDIAN(10, 20, 30, 40, 50)

Result:

Returns 30, the middle value. For odd count, median is the middle value when sorted: 10, 20, 30, 40, 50.

VBA Implementation & Automation

Basic MEDIAN in VBA

Use MEDIAN function in VBA

' Basic MEDIAN in VBA
Range("C1").Value = Application.WorksheetFunction.Median(Range("A1:A10"))
' Returns: Median value

' Calculate median
Sub CalculateMedian()
    Dim medianValue As Double
    medianValue = Application.WorksheetFunction.Median(Range("A1:A10"))
    Range("B1").Value = medianValue
End Sub

' Compare MEDIAN with AVERAGE
Sub CompareMedianAverage()
    Dim medianValue As Double
    Dim averageValue As Double
    medianValue = Application.WorksheetFunction.Median(Range("A1:A10"))
    averageValue = Application.WorksheetFunction.Average(Range("A1:A10"))
    Range("B1").Value = "Median: " & medianValue
    Range("B2").Value = "Average: " & averageValue
End Sub

' Calculate median across multiple ranges
Sub MedianMultipleRanges()
    Dim medianValue As Double
    medianValue = Application.WorksheetFunction.Median( _
        Range("A1:A10"), _
        Range("B1:B10"), _
        Range("C1:C10"))
    Range("D1").Value = medianValue
End Sub

' Analyze distribution
Sub AnalyzeDistribution()
    Dim medianValue As Double
    Dim averageValue As Double
    Dim difference As Double
    medianValue = Application.WorksheetFunction.Median(Range("A1:A10"))
    averageValue = Application.WorksheetFunction.Average(Range("A1:A10"))
    difference = averageValue - medianValue
    Range("B1").Value = "Median: " & medianValue
    Range("B2").Value = "Average: " & averageValue
    Range("B3").Value = "Difference: " & difference
    ' Large difference indicates skewed data
End Sub

Business Applications

Robust Analysis

Central tendency with outliers

=MEDIAN(data_range)

Statistical Analysis

Median in statistical analysis

=MEDIAN(sample_data)

Income/Salary Data

Median income analysis

=MEDIAN(income_data)

Quality Control

Median measurements

=MEDIAN(measurements)

Common Issues & Solutions

#NUM! Error

MEDIAN returns #NUM! when no numeric values

=IF(COUNT(A1:A10)>0, MEDIAN(A1:A10), "No data")

Solution: MEDIAN requires at least one numeric value. If all values are text or empty, it returns error. Use IF(COUNT(range)>0, MEDIAN(range), "No data").

Unexpected Result

MEDIAN seems incorrect

For even count: median = average of two middle values

Solution: MEDIAN sorts values internally. For even count, it averages two middle values. Verify by sorting data manually and checking middle value(s).

MEDIAN vs AVERAGE

Uncertainty about when to use MEDIAN

MEDIAN for skewed data, AVERAGE for normal distribution

Solution: Use MEDIAN when data has outliers or is skewed. MEDIAN is less affected by extreme values. AVERAGE includes all values equally.

Even Count Behavior

MEDIAN with even number of values

Even count: median = average of two middle values

Solution: For even count, MEDIAN averages the two middle values. Example: MEDIAN(10,20,30,40) = (20+30)/2 = 25. This is standard statistical behavior.