MedianIfs

B

Biz

Well-known Member
Joined
May 18, 2009
Messages
1,773
Office Version
  1. 2021
Platform
  1. Windows
Dear All,

I'm trying to calculate Median with multiple criteria. I found a code in https://danwagner.co/how-to-create-a-medianifs-user-defined-function-with-vba/
Medianifs is great function except it fails when work out Median # of Sales of Pears and Bananas refer to cell H40 and I have my desired answer using Median function in Cell I40.




Excel 2013 32 bit
ABCDEFGHI
1ProductSalesStoresCPT
2Apples926,5653,846433.0
3Pears656,3802,189367.0
4Pears299,5941,477342.0
5Apples355,9308,692215.0
6Oranges504,7151,129200.0
7Bananas690,3431,856400.1
8Pears772,6454,019386.0
9Bananas400,0256,987327.0
10Pears573,6145,285327.0
11Pears644,9855,853254.0
12Apples346,5529,571429.0
13Pears961,1165,798304.0
14Bananas487,4245,928214.0
15Oranges971,9731,053371.0
16Grapes301,1575,145271.0
17Oranges493,5332,960370.0
18Grapes631,5999,003457.0
19Oranges187,8697,326373.0
20Grapes624,8818,783497.0
21Apples743,5295,359420.0
22Grapes973,0223,301442.0
23Bananas706,7891,849399.9
24Apples649,0223,885475.0
25Oranges910,3525,950309.0
26Oranges172,5906,873200.0
27Apples716,2511,653387.0
28Apples337,0421,363346.0
29Apples113,5304,341207.0
30Apples528,3711,298279.0
31
32
33NeedFormulaMEDIANIFS ResultsExcel's MEDIAN value
34Median Cost Per Ton for Apples with <=4K Stores=MEDIANIFS(D2:D30,A2:A30,"=Apples",C2:C30,"<=4000")387387
35Median # of Stores for non-Pears, non-Apples, non-Oranges, non-Bananas (i.e. Grapes) with >500K Sales=MEDIANIFS(C2:C30,A2:A30,"<>Pears",A2:A30,"<>Apples",A2:A30,"<>Oranges",A2:A30,"<>Bananas",B2:B30,">500000")8,7838,783
36Median # of Sales for Oranges where Cost Per Ton = 200=MEDIANIFS(B2:B30,A2:A30,"=Oranges",D2:D30,"=200")338,653338,653
37Median Cost Per Ton for Pears with >5K Stores=MEDIANIFS(D2:D30,A2:A30,"Pears",C2:C30,">5000")304304
38Median # of Sales for Bananas where Cost Per Ton <=400=MEDIANIFS(B2:B30, A2:A30, "Bananas", D2:D30, "<=400")487,424487,424
39
40Median # of Sales of Pears and Bananas=MEDIANIFS(B2:B30,A2:A30,"Bananas",A2:A30,"Pears")#VALUE!650,683
examples
Cell Formulas
RangeFormula
H34=MEDIANIFS(D2:D30,A2:A30,"=Apples",C2:C30,"<=4000")
H35=MEDIANIFS(C2:C30,A2:A30,"<>Pears",A2:A30,"<>Apples",A2:A30,"<>Oranges",A2:A30,"<>Bananas",B2:B30,">500000")
H36=MEDIANIFS(B2:B30,A2:A30,"=Oranges",D2:D30,"=200")
H37=MEDIANIFS(D2:D30,A2:A30,"Pears",C2:C30,">5000")
H38=MEDIANIFS(B2:B30, A2:A30, "Bananas", D2:D30, "<=400")
H40=MEDIANIFS(B2:B30,A2:A30,"Bananas",A2:A30,"Pears")
I34=MEDIAN(D2,D24,D27,D28,D30)
I35=MEDIAN(C18,C20,C22)
I36=MEDIAN(B6,B26)
I37=MEDIAN(D10,D11,D13)
I38=MEDIAN(B9,B14,B23)
I40=MEDIAN(B3,B4,B8,B10,B11,B13,B7,B9,B14,B23)


Code: 
Public Function MEDIANIFS(median_range As Range, ParamArray range_and_criteria_pairs())


    Dim lngIdx As Long, lngMedianRowIdx As Long, lngCriteriaIdx As Long
    Dim strOperator As String
    Dim varThreshold As Variant, varAccumulator() As Variant
    ReDim varAccumulator(0)
    Dim blnAllMatched As Boolean
    
    '''''''''''''''
    'Guard clauses'
    '''''''''''''''


    'Check for an empty range
    If median_range Is Nothing Then
        MEDIANIFS = 0
        Exit Function
    End If


    'Check for an uneven number of additional arguments
    '(since we rely on range / criteria pairs)
    If ((UBound(range_and_criteria_pairs) - LBound(range_and_criteria_pairs)) Mod 2) = 0 Then
        MEDIANIFS = 0
        Exit Function
    End If


    'Check for additional range validity (i.e. make sure
    'each passed in criteria range has the same number of rows and
    'columns as the original range
    For lngIdx = LBound(range_and_criteria_pairs) To UBound(range_and_criteria_pairs) Step 2
        If range_and_criteria_pairs(lngIdx).Rows.Count <> median_range.Rows.Count Or _
           range_and_criteria_pairs(lngIdx).Columns.Count <> median_range.Columns.Count Then
            MEDIANIFS = 0
            Exit Function
        End If
    Next lngIdx


    '''''''''''''''''''
    'Process the range'
    '''''''''''''''''''


    'Loop through all rows in the target range
    For lngMedianRowIdx = 1 To median_range.Rows.Count
    
        'Reset the Match flag, which we will try to flip to True during the Critieria phase
        blnAllMatched = False


        'Loop through all the range / criteria pairs
        For lngCriteriaIdx = LBound(range_and_criteria_pairs) To UBound(range_and_criteria_pairs) Step 2


            'Identify the threshold and the operator for use in the criteria phase
            Select Case Left(range_and_criteria_pairs(lngCriteriaIdx + 1), 2)
                Case Is = "<="
                    strOperator = "<="
                    varThreshold = Val(Mid(range_and_criteria_pairs(lngCriteriaIdx + 1), 3))
                Case Is = ">="
                    strOperator = ">="
                    varThreshold = Val(Mid(range_and_criteria_pairs(lngCriteriaIdx + 1), 3))
                    
                'Not equal to (<>) is a little tricky as it could be a number oR a string,
                'so we need to check for both of these conditions
                Case Is = "<>"
                    strOperator = "<>"
                    If IsNumeric(Mid(range_and_criteria_pairs(lngCriteriaIdx + 1), 1)) And Not _
                       IsEmpty(range_and_criteria_pairs(lngCriteriaIdx + 1)) Then
                        varThreshold = Val(Mid(range_and_criteria_pairs(lngCriteriaIdx + 1), 3))
                    Else
                        varThreshold = UCase(Mid(range_and_criteria_pairs(lngCriteriaIdx + 1), 3))
                    End If
                    
                Case Else '<~ not a 2-character operator, check only the first character
                
                    Select Case Left(range_and_criteria_pairs(lngCriteriaIdx + 1), 1)
                        Case Is = "<"
                            strOperator = "<"
                            varThreshold = Val(Mid(range_and_criteria_pairs(lngCriteriaIdx + 1), 2))
                        Case Is = ">"
                            strOperator = ">"
                            varThreshold = Val(Mid(range_and_criteria_pairs(lngCriteriaIdx + 1), 2))
                        
                        'Equal (=) is a little tricky as it could be a number OR a string,
                        'so we need to check for both of these conditions
                        Case Is = "="
                            strOperator = "="
                            If IsNumeric(Mid(range_and_criteria_pairs(lngCriteriaIdx + 1), 1)) And Not _
                               IsEmpty(range_and_criteria_pairs(lngCriteriaIdx + 1)) Then
                                varThreshold = Val(Mid(range_and_criteria_pairs(lngCriteriaIdx + 1), 2))
                            Else
                                varThreshold = UCase(Mid(range_and_criteria_pairs(lngCriteriaIdx + 1), 2))
                            End If
                        
                        'For everything else, we are assuming equality (=) but without the
                        'explicit equal sign. This means the If / Else will be very similar
                        'to the
                        Case Else
                            strOperator = "="
                            If IsNumeric(range_and_criteria_pairs(lngCriteriaIdx + 1)) And Not _
                               IsEmpty(range_and_criteria_pairs(lngCriteriaIdx + 1)) Then
                                varThreshold = range_and_criteria_pairs(lngCriteriaIdx + 1)
                            Else
                                varThreshold = UCase(range_and_criteria_pairs(lngCriteriaIdx + 1))
                            End If
                    End Select
                    
            End Select
            
            'Criteria phase: check each cell in the passed-in ParamArray against the threshold
            With range_and_criteria_pairs(lngCriteriaIdx)
            
                'Check the operator (">=", "<=", "<", ">", "<>", and "=") against the cell
                Select Case strOperator
                    Case Is = "<>" '<~ check if cell does not equal the threshold
                        If UCase(.Cells(lngMedianRowIdx, 1)) <> varThreshold Then
                            blnAllMatched = True
                        Else
                            blnAllMatched = False
                        End If
                    Case Is = ">=" '<~ check if cell is greater than or equal to the threshold
                        If .Cells(lngMedianRowIdx, 1) >= varThreshold Then
                            blnAllMatched = True
                        Else
                            blnAllMatched = False
                        End If
                    Case Is = ">" '<~ check if cell is greater than the threshold
                        If .Cells(lngMedianRowIdx, 1) > varThreshold Then
                            blnAllMatched = True
                        Else
                            blnAllMatched = False
                        End If
                    Case Is = "<=" '<~ check if cell is less than or equal to the threshold
                        If .Cells(lngMedianRowIdx, 1) <= varThreshold Then
                            blnAllMatched = True
                        Else
                            blnAllMatched = False
                        End If
                    Case Is = "<" '<~ check if cell is less than the threshold
                        If .Cells(lngMedianRowIdx, 1) < varThreshold Then
                            blnAllMatched = True
                        Else
                            blnAllMatched = False
                        End If
                    Case Else '<~ equal is a special case, could be a number OR a string
                        
                        'Examine the numeric, non-blank case
                        If IsNumeric(.Cells(lngMedianRowIdx, 1)) And Not _
                           IsEmpty(.Cells(lngMedianRowIdx, 1)) Then
                            If Val(.Cells(lngMedianRowIdx, 1)) = varThreshold Then
                                blnAllMatched = True
                            Else
                                blnAllMatched = False
                            End If
                        
                        'Examine the string case
                        Else
                            If UCase(CStr(.Cells(lngMedianRowIdx, 1))) = varThreshold Then
                                blnAllMatched = True
                            Else
                                blnAllMatched = False
                            End If
                        End If
                End Select
            
            End With
            
            'If our flag has not been flipped to True for even a single iteration,
            'at least one condition has not been met. As such, the cell will
            'NEVER be accumulated!
            If Not blnAllMatched Then Exit For


        Next lngCriteriaIdx
        
        'Wahoo! If the row passed all criteria (and is numeric),
        'add the value to our accumulator
        If blnAllMatched Then
            With median_range
                If IsNumeric(.Cells(lngMedianRowIdx, 1)) And Not IsEmpty(.Cells(lngMedianRowIdx, 1)) Then
                    varAccumulator(UBound(varAccumulator)) = .Cells(lngMedianRowIdx, 1).Value
                    ReDim Preserve varAccumulator(UBound(varAccumulator) + 1)
                End If
            End With
        End If


    Next lngMedianRowIdx


    'Remove the last element from the accumulator, it's empty
    ReDim Preserve varAccumulator(UBound(varAccumulator) - 1)
    
    'Calculate the median
    MEDIANIFS = WorksheetFunction.Median(varAccumulator)
    
End Function



Your help would be greatly appreciated.

Kind Regards

Biz
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
H34, control+shift+enter:

=MEDIAN(IF($A$2:$A$30="apples",IF($C$2:$C$30<=4000,$D$2:$D$30)))

H35, control+shift+enter:

=MEDIAN(IF(ISNA(MATCH($A$2:$A$30,{"pears","apples","oranges","bananas"},0)),IF($B$2:$B$30>500000,$C$2:$C$30)))

H36, control+shift+enter:

=MEDIAN(IF($A$2:$A$30="oranges",IF($D$2:$D$30=200,$B$2:$B$30)))

H37, control+shift+enter:

=MEDIAN(IF($A$2:$A$30="pears",IF($C$2:$C$30 > 5000,$D$2:$D$30)

H38, control+shift+enter:

=MEDIAN(IF($A$2:$A$30="bananas",IF($D$2:$D$30 <= 400,$B$2:$B$30)))

H40, control+shift+enter:

=MEDIAN(IF(ISNUMBER(MATCH($A$2:$A$30,{"bananas","pears"},0)),$B$2:$B$30))
 
Upvote 0
Hi Aladin,

Your formulas works but I'm trying to avoid array formulas as it would slow calculations.

Trying to use an Excel UDF.

Kind Regards

Biz
 
Upvote 0
Biz said:
Hi Aladin,

Your formulas works but I'm trying to avoid array formulas as it would slow calculations.

Trying to use an Excel UDF.

Kind Regards

Biz
Click to expand...


I'm not sure an UDF would be faster than these array-processing median formulas. If you take the UDF route, you need to modify that function to admit manipulation of the ranges it is fed with.
 
Upvote 0
Hi Biz,

Does not this UDF combines conditions by AND operator rather than by OR operator?
If so then in the call =MEDIANIFS(B2:B30,A2:A30,"Bananas",A2:A30,"Pears")
the combined condition (A2:A30="Bananas") And (A2:A30,"Pears") will never happen

Best Regards
 
Upvote 0
After this line of the code: Next lngMedianRowIdx
insert one more code line: If UBound(varAccumulator) = 0 Then Exit Function
As a result the default zero value will be returned in such a case.
 
Upvote 0
Hi Vladimir,

Thank you very much for your help again.

You are correct about AND operator logic and used your Exit Function suggestion in post 6.

The revised formula Cell H 40 derives Median # of Sales of Pears and Bananas

=MEDIANIFS(B2:B30,A2:A30,"<>Apples",A2:A30,"<>Grapes",A2:A30,"<>Oranges")

Kind Regards

Biz
 
Last edited:
Upvote 0
Hello,
i was trying use your super function MEDIANIFS but there is one problem. It works only on vertical data. I would like to use on horizontally date.. Do you think its possible to create. Thank you very much for you help it is very uselfull. Do I something wrong?

 
Upvote 0
Merlin99 said:
Hello,
i was trying use your super function MEDIANIFS but there is one problem. It works only on vertical data. I would like to use on horizontally date.. Do you think its possible to create. Thank you very much for you help it is very uselfull. Do I something wrong?
Click to expand...

Why not invoke a native formula, consisting of MEDIAN(IF(...)) ?
 
Upvote 0
You must log in or register to reply here.

Similar threads

Saher Naji
  • Question
VBA Code Issue on Mac Platform (working perfectly on Windows)
Replies
4
Views
304
Cristian Buse
C
H
  • Question
Compare Demand Data to Stock Data
Replies
6
Views
367
Bosquedeguate
Bosquedeguate
L
  • Solved
Would like to make edits to VBA Calendar Code
Replies
8
Views
435
Legacy 352679
L
G
  • Solved
Format & Protect range of cells using VBA
Replies
7
Views
324
Gajendran Yadhav
G
E
  • Question
This code is to put a excel range onto a email
Replies
3
Views
417
I_know_nuffin
I

Forum statistics

Threads
1,215,461
Messages
6,124,956
Members
449,200
Latest member
indiansth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top