Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Median with Quantity Column
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Median with Quantity Column
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Median with Quantity Column
If values are in A2:A8 and quantities are in B2:B8, try normally entered:
=LOOKUP(-0.5,-PROB(ROW(A2:A8),B2:B8/SUM(B2:B8),ROW(A2:A8),2^20),A2:A8) This is equivalent to looking up the 50% mark within a column of cumulative percentages. (Data is ordered so that the lookup function can be applied.) "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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need a formula to increase quantity of one cell until the quantity ofanother is exceeded | Excel Discussion (Misc queries) | |||
Count quantity of repeated items in a column | Excel Discussion (Misc queries) | |||
Return the row number of Median value of a column | Excel Worksheet Functions | |||
Rank a quantity column using a pivot table | Excel Discussion (Misc queries) | |||
Can I add a quantity to every cell in a column using a formula? | Excel Discussion (Misc queries) |