# How to Calculate the Median in Different Cases in Excel

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

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

``````

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

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.

## 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])….