Does anyone know of a UDF that performs a "Subtotal" median function?

[jbaich]

Hi all, there are many threads that reference this formula or something similar with the goal of emulating the Subtotal behaviour for Median. In my case, specifically I'm wanting the median function to only apply to visible cells when I'm using filters. The formula that I've been using and seems to work well is

{=MEDIAN(IF(SUBTOTAL(2,OFFSET(A2,ROW(A2:A500)-ROW(A2),0)),A2:A500))}

Obviously in this example the data range is A2:A500, I was wondering if anyone knows of a user defined function around this formula that will populate the range values based on the user selection like a regular function does? I was thinking something like;

Function MEDIANSUBTOTAL (datarange)

where if I entered "=MEDIANSUBTOTAL(S4:S30)" into my formula bar, the function would perform the operation as though I'd entered

{=MEDIAN(IF(SUBTOTAL(2,OFFSET(S4,ROW(S4:S30)-ROW(S4),0)),S4:S30))}

Has anyone already created or found a UDF for this function/purpose?

Thanks,
Joe

 

[AlphaFrog]

I don't think a UDF would automatically recalculate from a change in filtered rows.

 

[Domenic]

Actually, I tested it and it seems to work. So maybe something like this...

Function MEDIANSUBTOTAL(r As Range)
    Dim v As Variant
    v = Evaluate("MEDIAN(IF(SUBTOTAL(2,OFFSET(" & r.Address & ",ROW(" & r.Address & ")-MIN(ROW(" & r.Address & ")),0[COLOR=#ff0000],1[/COLOR]))," & r.Address & "))")
    If IsError(v) Then
        MEDIANSUBTOTAL = CVErr(xlErrNum)
    Else
        MEDIANSUBTOTAL = v
    End If
End Function

Note that I've included the height argument (in red) for OFFSET.

Hope this helps!

 

[jbaich]

Thank You! Thank You! Thank You!

So far it seems to work perfectly!

You just made my month!

Cheers,
Joe

 

[Domenic]

You're very welcome. Glad I could help.

Cheers!