ExcelBanter - Calculating the Median Price Sold

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Calculating the Median Price Sold (https://www.excelbanter.com/excel-worksheet-functions/199904-calculating-median-price-sold.html)

Calculating the Median Price Sold

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

Calculating the Median Price Sold

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

Calculating the Median Price Sold

Found it

With respect to the original author
David Hager

Mike

"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

Calculating the Median Price Sold

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