How to Calculate MEDIAN IF in Excel

puneet-gogia-excel-champs

- Written by Puneet

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.

median-with-if

What is a Median – It is the value that divides a list of numbers into two parts. In other words, half the numbers are less than the median, and half are greater. If there’s an odd number of values, the median is the middle one. 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 set of data.

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:

calculate-median-if
  1. First, enter the MEDIAN function in a cell.
  2. After that, enter the IF function with the MEDIAN.
  3. Now, in the (logical_test) argument refer to the range A1:A1.
  4. From here in the same argument enter an equal to the operator and enter “Day 1” to use as a condition to test.
  5. Next, in the (value_if_true) in IF, refer to the range B1:B12 where you have data to calculate MEDIAN.
  6. 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 when entering a formula as an array, it works on the entire range instead of a single cell. You need to break this formula into two parts to understand this.

understands-the-formula

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”, then the array does not include the corresponding value from the range B1:B12.

condition-to-test-in-first-part

After that, in the second argument of the IF function, you have referred to the range B1:B12 where we have the data to calculate the median.

referred-to-range-in-second-part

In the SECOND PART, uses 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 a 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 this formula 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 to copy the below code and then 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

Get the Excel File

Last Updated: April 28, 2024