How To Find Median For A Huge Data By Months

[Karthik]

Hi,


I've a huge data set with IDs & Spend by month & regions. (https://docs.google.com/spreadsheet/...hl=en_US#gid=0)


I would like to fill the table which is to the right of the big table, information related to each of the two regions by month. Is there a formula (subtotal/sumproduct, I'm guessing) that can give me the info I'm looking for?


Thanks much in advance!

 

[NARAYANK991]

Hi Karthik ,


The link doesn't seem to work ; can you repost ?


Narayan

 

[Karthik]

So, sorry about that - I couldn't past the entire link here.


Here you go this time - https://docs.google.com/spreadsheet/ccc?key=0Ao7RzFobcTErdEJ1OTl2d0dWWEJxaW1CaGxQR0hzZ2c&hl=en_US


LMK if you need any further details!

 

[Luke M]

The dates in H2 and I2 need to be corrected (inputted as 9th of month, not 2009, but I think this is the formula you want in H3:


=MEDIAN(IF(($D$2:$D$300=$G3)*($E$2:$E$300=TEXT(H$2,"mmm-yy")),$B$2:$B$300))


Note that this is an array formula, and needs to be confirmed using Ctrl+Shift+Enter.

The only thing I see that gets tricky is making sure your dates are being compared correctly, but other than that it should be pretty straightforward.

 

[Karthik]

Thanks Luke, that worked like MAGIC!

 

[Luke M]

You're very welcome, Karthik.