calculate median excel having conditions

I'm looking for a excel formula which will help me calculate the medians of different data.

1   45 
2   54
3   26
4   12
1   34
2   23
3   9

Now, I need to calculate the median of data from B开发者_如何学JAVA1:B4 and then B5:B8, and print whether the number is lesser/equal/greater than the median.. I've come up with preliminary formula

=IF(MEDIAN($B$1:$B$4)<B1;"g";IF(MEDIAN($B$1:$B$4)=B1;"e";"l"))

But, this won't help for calculating the median for different sets.

What should i do?

Thanks for the help!

To check if B1 is greater, less than, or equal the median of both the medians of B1:B4 and B5:B8 (in this case would be 29.25), then you could use something like this:

=IF(B1>(MEDIAN(MEDIAN(B1:B4),MEDIAN(B5:B7))),"g",IF(B1=(MEDIAN(MEDIAN(B1:B4),MEDIAN(B5:B7))),"e","l"))

If you just want to check against B1:B4 (as in your example) you can use:

=IF(B1>MEDIAN(B1:B4),"g",IF(B1=MEDIAN(B1:B4),"e","l"))

UPDATE: According to your comment below, here is what you can write in C1 and drag down to C4:

=IF(B1>MEDIAN($B$1:$B$4),B1&">"&MEDIAN($B$1:$B$4),IF(B1=MEDIAN($B$1:$B$4),B1&"="&MEDIAN($B$1:$B$4),B1&"<"&MEDIAN($B$1:$B$4)))

You have three problems here:

1. your 1st column is a sequence rather than a group identifier
2. there is no =MEDIANIF()
3. Pivot tables don't support Medians either

not a good starting point ....

ad #1, you could change your 1,2,3,4,1,2,3, ... towards 1,1,1,1, 2,2,2, ... to indicate what belongs together

ad #2, #3 ... I would suggest to define a function =MEDIANIF() in VBA; example:

Function MedianIf(R As Range, Cr As Variant) As Variant
Dim Idx As Integer, Jdx As Integer, A() As Variant, CCnt As Integer, Tmp As Variant

    ' find array size
    CCnt = 0
    For Idx = 1 To R.Rows.Count
        If R(Idx, 1) = Cr Then CCnt = CCnt + 1
    Next Idx

    'dim array
    ReDim A(CCnt - 1)

    ' load from range into array
    CCnt = 0
    For Idx = 1 To R.Rows.Count
        If R(Idx, 1) = Cr Then
            A(CCnt) = R(Idx, 2)
            CCnt = CCnt + 1
        End If
    Next Idx

    ' bubble sort
    For Jdx = CCnt - 1 To 0 Step -1
        For Idx = 0 To Jdx - 1
            If A(Idx) > A(Idx + 1) Then
                Tmp = A(Idx)
                A(Idx) = A(Idx + 1)
                A(Idx + 1) = Tmp
            End If
        Next Idx
    Next Jdx

    ' get Median
    If CCnt Mod 2 = 1 Then
        MedianIf = A(Int(CCnt / 2))
    Else
        MedianIf = (A(CCnt / 2 - 1) + A(CCnt / 2)) / 2
    End If

End Function

Use this function by selecting a range 2 col's wide and x rows down, like

=MedianIF($A$1:$B$7,A1)

and there we go ... now you can use the new function in your =IF(.... , "g", ...)

so one single formula for all the range .... attention: bubble sort is not very economic for large ranges