Excel - Median of column I if column A numbers match

Asked By Abbey Co. on 20-Nov-09 04:12 PM
I am using the 2007 version of Excel.  I have a worksheet with 13,000 lines of
data.  Column A is titled ITEM and column I is titled PRICE.  I need to find
the Median number of column I when the numbers in column A are the same.  I
have 2000 unique ITEM numbers.  When the ITEM number in several rows match, I
need a formula to find the Median value of the PRICE.

So if A2, A3, A4 ... A20, and A21 all have the same ITEM number, then I need
the formula to find the Median number of I2, I3, I4 ... I20, and I21.

I have messed around with IF functions and cannot seem to get it to work.
Thank you in advance for your help.




Luke M replied to Abbey Co. on 20-Nov-09 04:41 PM
Something similar to this array* function:

=MEDIAN(IF(A2:A200=1234,B2:B200))

*Array formulas must be confirmed using Ctlr+Shift+Enter, not just Enter.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*
T. Valko replied to Abbey Co. on 20-Nov-09 05:29 PM
If the data is sorted so that the items are grouped together:

Item1
Item1
Item1
Item2
Item2
Item2
Item3
Item3

This normally entered formula is much more efficient than an array formula
on that much data.

K2 = some item

=MEDIAN(INDEX(Price,MATCH(K2,Item,0)):INDEX(Price,MATCH(K2,Item,0)+COUNTIF(Item,K2)-1))

Where:

Price refers to I2:I13000
Item refers to A2:A13000


--
Biff
Microsoft Excel MVP