I had tlo use a UDF to make the array of numbers

A B
1 5
2 8
3 7

The udf below will create an array (5,8,8,7,7,7)

=Median(MakeArray(A1:B3)

Function MakeArray(target As Range)
Dim MyArray()

ArraySize = 0
For RowCount = 1 To target.Rows.Count
For Count = 1 To target(RowCount, 1)

ReDim Preserve MyArray(0 To ArraySize)
MyArray(ArraySize) = target(RowCount, 2)
ArraySize = ArraySize + 1
Next Count
Next RowCount

MakeArray = MyArray

End Function





"SDShannonS" wrote:

How do I calculate the Median value when using a separate Quantity column?

For example, say there are two columns, Quantity and Value. There are two
rows intersecting those columns which represent 99 purchases at $1 each
(Quantity: 99, Value 1) and 1 purchase at $99 (Quantity: 1, Value: 99). The
actual Median of those 100 purchases would be right around $1, but if you use
the Median function on the Value column, you're going to get around $50
because it's just seeing two values, 1 and 99.

So, how do you get the MEDIAN function to weight 99 separate 1's and a
single 99?


Shannon