If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Median of column I if column A numbers match
I'm 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've messed around with IF functions and can't seem to get it to work. Thank you in advance for your help. |
#2
|
|||
|
|||
Median of column I if column A numbers match
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!* "Abbey Co." wrote: I'm 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've messed around with IF functions and can't seem to get it to work. Thank you in advance for your help. |
#3
|
|||
|
|||
Median of column I if column A numbers match
I have a worksheet with 13,000 lines of data.
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)) Whe Price refers to I2:I13000 Item refers to A2:A13000 -- Biff Microsoft Excel MVP "Abbey Co." Abbey wrote in message ... I'm 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've messed around with IF functions and can't seem to get it to work. Thank you in advance for your help. |
Thread Tools | |
Display Modes | |
|
|