Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9,101
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 272
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,059
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need a formula to increase quantity of one cell until the quantity ofanother is exceeded rudyeb Excel Discussion (Misc queries) 1 October 24th 08 03:34 PM
Count quantity of repeated items in a column Susienak Excel Discussion (Misc queries) 4 July 31st 08 05:05 PM
Return the row number of Median value of a column J.Y.Joe Excel Worksheet Functions 2 May 15th 08 09:23 PM
Rank a quantity column using a pivot table Pete Excel Discussion (Misc queries) 0 March 5th 07 11:44 PM
Can I add a quantity to every cell in a column using a formula? Marimar Excel Discussion (Misc queries) 4 July 27th 06 07:00 PM


All times are GMT +1. The time now is 06:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"