Median Calculation on multiple criteria

[Commander Vimes]

So i've been struggling with this. Is there a way to calculate the median of a set of values based on Name and volume? An example of the data layout is below, what i want is to calculate the median value for a large table with multiple entries using formula or VBA.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Volume[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]1[/TD]
[TD]2.5[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]2[/TD]
[TD]3.6[/TD]
[/TR]
[TR]
[TD]Duck[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

Using the example above i would want to calculate the median for Dog, Cat and Duck.

Using Cat as an example, the figures there would be 1 x Cat=2 and 2 xCat=3.6.

Median would be 3.6: (2, 3.6, 3.6)

So is there a formula or macro Vba which would look at the Name and volume and then calculate the Mean of the value for each unique Name?

-CV

 

[sericom]

Here's a function that should do it

Public Function cMEDIAN(ByVal cText As String, ByVal cRng As Range, Optional cVolume As Long = 1, Optional cValue As Long = 2)
    Application.Volatile True
    Dim cValues As Variant
    Dim i As Long
    i = 0
    ReDim cValues(i)
    For Each c In cRng
        If LCase(c.Value) = LCase(cText) Then
            For j = 1 To c.Offset(, cVolume).Value
                cValues(i) = c.Offset(, cValue).Value
                i = i + 1
                ReDim Preserve cValues(i)
            Next
        End If
    Next
    cMEDIAN = WorksheetFunction.Median(cValues)
End Function

Example usage if your data started in A1

=cMEDIAN("Cat",A2:A7)

There is optional parameters there to offset the Volume and Value column if you need them.

 

[Commander Vimes]

Thanks Sericom, This looks great, just testing it out now.

As you mentioned, if the Vol and Val cols were both in Col C,D respectively rather than in B,C. Would i just need to add a +1 to the offset parameters. (if they were further along just increase as required?)

Are you able to elaborate on how this works? In particular how it picks up the Volume and then calcs the Median?

 

[sericom]

If Name, Volume and Order are next to each other in that order, you won't need to add parameters. Doesn't matter if they aren't in Col A, just as long as they are grouped together.
The parameters are for offsetting the column from the name column.
eg. If name is in col A, Vol in col C and Val in Col D, then Vol is 2 columns away from A, and Val is 3 away from name so you'd use the following

=cMEDIAN("Cat",A2:A7,2,3)