Median formula with multiple IF in an array involving dates.

[ShyamExcel]

I'm working on something that i need some help with.


I don't see an option to attach a sample Excel file, so here is a sample. Runs from A3 to A354 excluding the heading(heading on A2 and B2)


Week Start Volume

1/2/2006 477

1/9/2006 391

1/16/2006 303

1/23/2006 386

1/30/2006 595

2/6/2006 1051

2/13/2006 1158

2/20/2006 1026

2/27/2006 1275

3/6/2006 1207

3/13/2006 1066

3/20/2006 1173

3/27/2006 1092

4/3/2006 1090

4/10/2006 587

4/17/2006 620

4/24/2006 727


It has 2 columns. One is a date and a metric named volume.


It has data from 2006 to 2012. I'm looking to find trends in growth over time.


I thought of finding median for every year and then look at YoY growth%. I wrote an array formula like this for finding median when the date is from year 2006 {=MEDIAN(IF(A3:A354<="1/31/2006"+0,B3:B354))}


This worked great. I found that +0 thing in a site.


But when I got to 2007, I had to use both less and than greater than for dates and the formula kept giving error.


{=MEDIAN(IF(AND(A3:A354<="12/30/2007"+0),(A3:A354>"1/1/2007"+0)),B3:B354)}


Can you help me figure out where I'm going wrong? When the cell contains dates, I thought excel does greater than or less than operations.


I have a lots of such data sets for various products to calculate for, so hoping this will can help me get the Median for each of the 6 years and then I could just do a YoY growth percent calculation on the medians.


Any ideas welcome!


Thanks

Shyam

 

[Montrey]

Have you tried median and sumproduct?

 

[Debraj]

Hi Shyam,


Can you please try the below for me


{=MEDIAN(IF(($A$3:$A$354 > DATEVALUE("01/01/2007"))*($A$3:$A$354 <= DATEVALUE("30/12/2007")),$B$3:$B$354))}


Regards,

Deb

 

[ShyamExcel]

@Debraj Even DateValue is giving the same #Value error.

@Montrey. Not tried Sum product though

 

[Debraj]

Hi Shyam,


Problem is with your Date Column.. i.e Column 1..

Please select your Date Column.. and press "Ctrl + Shift + 1".. and check which are Text and which are Date (Date should be changed to Numeric.. but Text will Not)..


If you have found some of them text and some of them Date.. then..

* Select Date Column..

* GoTo Text to Column.

* Click Next.Next.

* In the Next Screen.. Change General to Date > MDY..

* Finish..


It will convert all your Non Date to Date.. in the same area..


If still not resolved then.. attach a sample file.. or check you ControlPanel Regional Setting to set your desired Date Format (MM-DD-YYYY)..


To attache a file.. read 2nd Green Stick in the main Forum Page..


Regards,

Deb

 

[Hui]

ShyamExcel


Can you post a sample file, Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[Hui]

Once you follow Debraj's advice to fix the dates above, use:

=SUMPRODUCT((YEAR(A3:A354)=2007)*(B3:B354))

 

[NARAYANK991]

Hi Shyam ,


Please remember that what you want is an array formula. The moment you use a logical such as an AND , OR ,.. you immediately convert the formula to a non-array formula.


When you need to have multiple criteria , use multiple IF statements.


See the difference between the following two IF statements :


=MEDIAN(IF(A2:A354>="1/31/2006"+0,IF(A2:A354<="2/28/2006"+0,B2:B354)))


=MEDIAN(IF(AND(A2:A354>="1/31/2006"+0,A2:A354<="2/28/2006"+0),B2:B354))


Even if you enter both formulae as array formulae , using CTRL SHIFT ENTER , only one of them will give you the correct answer.


Narayan

 

[ShyamExcel]

Beautiful! I cleaned up the dates and the array formula works like a charm!


Good learning about not mixing up logical operators with Array formulas. Thanks Deb, Narayan and Hui!