To write the MEDIAN IF formula in Excel you need to use the MEDIAN combined with the IF Function. First, you need to use the MEDIAN function, and then to create a condition you need to enter the IF function.
In the IF, refer to the range on which you want to test the condition. After that, refer to the range where you have the data. In the end, enter the formula as an array formula.
In Excel, there isn’t a built-in function specifically called MEDIANIF, unlike the AVERAGEIF or SUMIF functions.
However, suppose you need to calculate the median for a subset of data based on certain criteria. In that case, you can write a MEDIANIF formula using an array formula or with the help of other functions like IF.
What is a Median – The value divides a list of numbers into two parts. In other words, half the numbers are less than the median, and half are greater. The median is the middle if there’s an odd number of values. If there’s an even number, the median is the average of the two middle numbers. It’s a handy way to find the middle point in a data set.
In this tutorial, we will learn to write a formula to calculate a MEDIANIF.
Calculate MEDIANIF in Excel with a Conditional Formula
You can use the below steps:
- First, enter the MEDIAN function in a cell.
- After that, enter the IF function with the MEDIAN.
- Now, in the (logical_test) argument refer to the range A1:A1.
- From here in the same argument enter an equal to the operator and enter “Day 1” to use as a condition to test.
- Next, in the (value_if_true) in IF, refer to the range B1:B12 where you have data to calculate MEDIAN.
- In the end, enter closing parentheses to close the function. And use Ctrl + Shift + Enter to get the result from the formula.
=MEDIAN(IF(A1:A12="Day 1",B1:B12))
Regular formulas in Excel calculate a single value from multiple values. But, array formulas can perform various calculations and handle arrays of values as input and output. By pressing Ctrl+Shift+Enter, you tell Excel that the formula should be evaluated as an array formula, which allows it to process multiple values.
How this Formula Works
To understand this formula, you need to understand that entering a formula as an array works on the entire range instead of a single cell. You need to break this formula into two parts to understand it.
In the FIRST PART, IF(A1:A12=”Day 1″,B1:B12), uses the IF function. This function tests a condition for each cell in the range A1:A12 to see if it contains the text “Day 1”.
If a cell in this range does contain “Day 1”, then the corresponding value in the same row from the range B1:B12 is included in an array.
And if a cell in the range A1:A12 does not contain “Day 1,” the array does not include the corresponding value from the range B1:B12.
After that, in the second argument of the IF function, you refer to the range B1:B12, where we have the data to calculate the median.
In the SECOND PART, use the MEDIAN function. This function calculates the median value of the array created by the IF function.
The median is the middle value in a set of values. If the set of values has an even number, the median is the average of the two middle numbers.
But Can I Use Helper Columns for MEDIANIF?
Yes, if you prefer not to use array formulas, you can use a helper column to filter out the values first based on the criterion and then apply the median function to the filtered value.
In a new column (say C), you can enter the following formula next to each row in your dataset and then drag it down to apply it to other rows.
=IF(A2="Day1", A1, "")
Now, you can simply calculate the median on this helper column:
=MEDIAN(C1:C12)
This will return the median of all values that meet the criteria without requiring an array formula.
Write a User Define Function for MEDIANIF
Below is an example of a VBA function that you can use to calculate the median of a range of numbers based on a specific condition in another range.
All you need to do is copy the code below and paste it into a new module in the Visual Basic editor: Developer Tab > Visual Basic Editor > Insert Module > Paste the Code > Enter the Formula in the Worksheet.
=MEDIANIF(dataRange, criteriaRange, criterion)
Function MEDIANIF(dataRange As Range, criteriaRange As Range, criterion As Variant) As Variant
Dim cell As Range
Dim medianArray As Collection
Set medianArray = New Collection
' Collect all numbers that meet the criterion
Dim i As Long
For i = 1 To dataRange.Cells.Count
If criteriaRange.Cells(i).Value = criterion Then
medianArray.Add dataRange.Cells(i).Value
End If
Next i
' Handle case where no data meets the criterion
If medianArray.Count = 0 Then
MEDIANIF = "No data matches criteria"
Exit Function
End If
' Copy the collection to an array for sorting
Dim sortedArray() As Double
ReDim sortedArray(1 To medianArray.Count)
For i = 1 To medianArray.Count
sortedArray(i) = medianArray(i)
Next i
' Sort the array
Call QuickSort(sortedArray, LBound(sortedArray), UBound(sortedArray))
' Calculate median
Dim mid As Integer
mid = UBound(sortedArray) \ 2
If UBound(sortedArray) Mod 2 = 0 Then
' Even number of items; return average of two middle elements
MEDIANIF = (sortedArray(mid) + sortedArray(mid + 1)) / 2
Else
' Odd number of items; return the middle element
MEDIANIF = sortedArray(mid + 1)
End If
End Function
' Utility function for sorting the array
Sub QuickSort(arr As Variant, first As Long, last As Long)
Dim pivot As Double, temp As Double
Dim i As Long, j As Long
If first >= last Then Exit Sub
pivot = arr((first + last) \ 2)
i = first
j = last
While i <= j
While arr(i) < pivot And i < last
i = i + 1
Wend
While arr(j) > pivot And j > first
j = j - 1
Wend
If i <= j Then
temp = arr(i)
arr(i) = arr(j)
arr(j) = temp
i = i + 1
j = j - 1
End If
Wend
Call QuickSort(arr, first, j)
Call QuickSort(arr, i, last)
End Sub