The median is the number in the middle of a range of data by order, half of the data in this range is larger than it, and half is smaller than it. Sometimes we want to calculate the median for a range of data. This article will introduce you the formula to calculate the median for a range in some different cases in excel.

1. Calculate the Median for A Range Including Zero Number using Formula

See example below:

Step 1: In any blank cell, enter the formula :

=MEDIAN(A1:B3).

Step 2: Click Enter to get the result. Verify that zero number is included in calculating.

2. Calculate the Median for A Range Including Zero Number (VBA Code)

Moving on to the second method, let’s explore using VBA code to calculate the median for a range, including zero numbers.

Press ‘Alt + F11‘ to open the Visual Basic for Applications editor.

Right-click on the project in the editor, select ‘Insert‘ and choose ‘Module‘ to add a new module.

Copy the provided VBA code and paste it into the module.

Function MedianIncludingZero(rng As Range) As Double
    Dim values() As Double
    Dim i As Integer
    Dim totalValues As Integer

    totalValues = 0

    ' Loop through each cell in the range
    For Each cell In rng
        If IsNumeric(cell.Value) Then
            totalValues = totalValues + 1
            ReDim Preserve values(1 To totalValues)
            values(totalValues) = cell.Value
        End If
    Next cell

    ' Sort the values array
    Call QuickSort(values, 1, UBound(values))

    ' Calculate the median
    If totalValues Mod 2 = 1 Then
        MedianIncludingZero = values((totalValues + 1) / 2)
    Else
        MedianIncludingZero = (values(totalValues / 2) + values(totalValues / 2 + 1)) / 2
    End If
End Function

' QuickSort algorithm
Sub QuickSort(arr() As Double, low As Integer, high As Integer)
    Dim pivot As Double
    Dim temp As Double
    Dim i As Integer
    Dim j As Integer

    If low < high Then
        pivot = arr(high)
        i = low - 1

        For j = low To high - 1
            If arr(j) <= pivot Then
                i = i + 1
                temp = arr(i)
                arr(i) = arr(j)
                arr(j) = temp
            End If
        Next j

        temp = arr(i + 1)
        arr(i + 1) = arr(high)
        arr(high) = temp

        Call QuickSort(arr, low, i)
        Call QuickSort(arr, i + 2, high)
    End If
End Sub

Close the VBA editor and return to your Excel workbook.

In a cell, enter the formula:

=MedianIncludingZero(A1:B3)

Press Enter to execute the VBA function and calculate the median, including zero.

The cell will display the median, inclusive of zero, for the specified range.

3. Calculate the Median for A Range Excluding Zero Number using Formula

We still use above example to calculate the median, but this time zero numbers will be ignored in calculating.

Step 1: In any blank cell, enter the formula:

 =MEDIAN(IF(A1:B3>0,A1:B3))

Step 2: Press ‘Ctrl + Shift + Enter’ to get the result. Verify that zero number is excluded in calculating.

4. Calculate the Median for A Range Excluding Zero Number (VBA Code)

Moving on to the fourth method, let’s explore using VBA code to calculate the median for a range, excluding zero numbers.

Press ‘Alt + F11’ to open the Visual Basic for Applications editor.

Right-click on the project in the editor, select ‘Insert’ and choose ‘Module’ to add a new module.

Copy the provided VBA code and paste it into the module.

Function MedianExcludingZero(rng As Range) As Double
    Dim arr() As Variant
    Dim i As Integer

    For i = 1 To rng.count
        If rng.Cells(i).Value <> 0 Then
            MedianExcludingZero = MedianExcludingZero + rng.Cells(i).Value
        End If
    Next i

    MedianExcludingZero = MedianExcludingZero / WorksheetFunction.CountIf(rng, "<>0")
End Function

Close the VBA editor and return to your Excel workbook.

In a cell, enter the formula:

=MedianExcludingZero(A1:B3)

Press Enter to execute the VBA function and calculate the median, excluding zero.

The cell will display the median, excluding zero, for the specified range.

5. Calculate the Median for Multiple Ranges using Formula

See the table below:

You can see that there are two ranges of data in two tables. So, if we want to calculate the median for the two ranges, you can follow below steps to calculate the median.

Step 1: In any blank cell, enter the formula

=MEDIAN(A1:B3,A5:B7)

If there are multiple ranges exist, we can use a comma to separate different ranges.

Step 2: Click Enter to get the result.

6. Calculate the Median for Multiple Ranges (VBA Code)

Lastly, let’s explore using VBA code to calculate the median for multiple ranges in Excel.

Press ‘Alt + F11’ to open the Visual Basic for Applications editor.

Right-click on the project in the editor, select ‘Insert,’ and choose ‘Module’ to add a new module.

Copy the provided VBA code and paste it into the module.

Function MedianForMultipleRanges(ParamArray ranges() As Variant) As Double
    Dim cell As Range
    Dim i As Integer
    Dim values() As Double
    Dim totalValues As Integer

    totalValues = 0

    ' Loop through each specified range
    For i = LBound(ranges) To UBound(ranges)
        For Each cell In ranges(i)
            If IsNumeric(cell.Value) Then
                totalValues = totalValues + 1
                ReDim Preserve values(1 To totalValues)
                values(totalValues) = cell.Value
            End If
        Next cell
    Next i

    ' Sort the values array
    Call QuickSort(values, 1, UBound(values))

    ' Calculate the median
    If totalValues Mod 2 = 1 Then
        MedianForMultipleRanges = values((totalValues + 1) / 2)
    Else
        MedianForMultipleRanges = (values(totalValues / 2) + values(totalValues / 2 + 1)) / 2
    End If
End Function

' QuickSort algorithm
Sub QuickSort(arr() As Double, low As Integer, high As Integer)
    Dim pivot As Double
    Dim temp As Double
    Dim i As Integer
    Dim j As Integer

    If low < high Then
        pivot = arr(high)
        i = low - 1

        For j = low To high - 1
            If arr(j) <= pivot Then
                i = i + 1
                temp = arr(i)
                arr(i) = arr(j)
                arr(j) = temp
            End If
        Next j

        temp = arr(i + 1)
        arr(i + 1) = arr(high)
        arr(high) = temp

        Call QuickSort(arr, low, i)
        Call QuickSort(arr, i + 2, high)
    End If
End Sub

Close the VBA editor and return to your Excel workbook.

In a cell, enter the formula:

=MedianForMultipleRanges(A1:B3,A5:B7)

Press Enter to execute the VBA function and calculate the median for multiple ranges.

The cell will display the calculated median for the combined data from multiple ranges.

7. Video: Calculate the Median for Multiple Ranges

This Excel video tutorial, we’ll explore diverse methods to calculate the median for a range, including and excluding zero numbers. Additionally, we’ll delve into calculating the median for multiple ranges. We employ both formulas and VBA code to tackle these scenarios, ensuring a comprehensive understanding of median calculations.

https://youtu.be/RTBnXiQwlSM

8. Related Functions

• Excel MEDIAN function
  The Excel AVERAGE function returns the median of the given numbers. And the median is the number in the middle of a list of supplied numbers.The syntax of the MEDIAN function is as below:=MEDIAN (number1, [number2], …)….
• Excel IF function
  The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….