A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Median of column I if column A numbers match



 
 
Thread Tools Display Modes
  #1  
Old November 20th 09, 09:12 PM posted to microsoft.public.excel.misc
Abbey Co.
external usenet poster
 
Posts: 1
Default 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.
Ads
  #2  
Old November 20th 09, 09:41 PM posted to microsoft.public.excel.misc
Luke M
external usenet poster
 
Posts: 2,722
Default 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  
Old November 20th 09, 10:29 PM posted to microsoft.public.excel.misc
T. Valko
external usenet poster
 
Posts: 15,768
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Median with Quantity Column SDShannonS Excel Worksheet Functions 3 May 25th 09 01:15 AM
Return text in Column A if Column B and Column K match jeannie v Excel Worksheet Functions 4 December 13th 07 07:36 PM
how to match the two column with numbers in excel? sasidharan Excel Discussion (Misc queries) 2 February 9th 07 02:01 PM
Match Column B numbers to Column A numbers Bad_manager Excel Worksheet Functions 1 August 14th 06 07:55 PM
column filtering to match identical numbers sammc2 Excel Discussion (Misc queries) 1 July 14th 05 11:59 PM


All times are GMT +1. The time now is 02:34 PM.


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