Mike,
Thanks for your assistance. It worked perfectly.
Adam
"Mike H" wrote:
> You need a UDF. I copied this a long time ago and can't remember the name of
> the original author so apologies to whoever it was.
>
> Alt +F11 to open VB editor, Double click 'This Workbook' and paste this in
> on the right.
>
> Function WeightedMedian(ValueRange As Range, WeightRange As Range)
> Dim MedianArray()
> On Error GoTo WrongRanges
> ArrayLength = Application.Sum(WeightRange)
> ReDim MedianArray(1 To ArrayLength)
> Counter = 0
> ArrayCounter = 0
> For Each ValueRangeCell In ValueRange
> LoopCounter = LoopCounter + 1
> FirstArrayPos = ArrayCounter + 1
> ArrayCounter = ArrayCounter + Application.Index(WeightRange,
> LoopCounter)
> For n = FirstArrayPos To ArrayCounter
> MedianArray(n) = ValueRangeCell.Value
> Next
> Next
> WeightedMedian = Application.Median(MedianArray)
> Exit Function
> WrongRanges:
> WeightedMedian = CVErr(2016)
> End Function
>
> call with
> =WeightedMedian(B1:B8,A1:A8)
> It returns 345
>
> Mike
>
>
> WrongRanges:
> WeightedMedian = CVErr(2016)
> End Function
>
>
> "AMB" wrote:
>
> > Hello,
> >
> > I am trying to find a reasonable way of calculating a median. I have a data
> > set that is about 20,000 lines long and it contains Quantity Sold and Unit
> > Price. I need to calculate out the Median Price Sold. Is there a way to
> > count the Unit Price mulitple times based on the Quantity Sold so that I end
> > up with an accurate Median?
> >
> > The dataset appears as follows:
> >
> > Quantity Sold Unit Price
> > 1 330.00
> > 5 300.00
> > 3 330.00
> > 5 360.00
> > 4 360.00
> > 4 330.00
> > 3 369.00
> > 1 374.00
> >
> > Regards,
> >
> > Adam
> >
> >
> > --
> > Adam Brody
> > National Business Analyst
|
Similar Threads
