Median with Quantity Column
"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.
One approach....
Create a range (may be hidden) that contains the cumulative quantity in
descending order. For example, if Quantity is in A1:A100, put the following
formula into X1 and copy down:
=A1+X2
This assumes that X101 is empty. If it might contain text, use =SUM(A1,X2).
Alternatively, put =A100 into X100.
Then if Value is in B1:B100, compute the median with the following formula:
=index(B1:B100, match(X1 / 2, X1:X100, -1))
----- original message -----
"SDShannonS" wrote in message
...
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
|