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** – 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:

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

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.

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.

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