Calc median over a date range.

**welchs101** · 09-28-2011, 10:41 AM

I have a file (see enclosed).

On the raw_data sheet i have a percentages column with the corresponding date columns.

If you look at the summary sheet i list a date range in row 2. Start date range is listed in A2 and end date range is listed in cell B2.

What i want is to calculate the median value of the percentage column based on the date values in cells A2 and B2. I am not sure how to do this.

**NBVC** · 09-28-2011, 11:01 AM

Try:

=MEDIAN(IF(Raw_Data!$C$2:$C$41>=A2,IF(Raw_Data!$C$2:$C$41<=B2,Raw_Data!$D$2:$D$41)))

confirmed with CTRL+SHIFT+ENTER not just ENTER

**welchs101** · 10-04-2011, 07:43 AM

works great! thanks.

I was just wondering ..........why? Why did it work?

I know that by hitting cntrl+shift+enter your using an array of something but i did not know you could do this on just any thing.

thanks again!!!!!!!!!!!!!!

**NBVC** · 10-04-2011, 08:43 AM

It is finding the Median based on conditions.. you can use this logic as if it were a form of SUMIF(S) or COUNTIF(S). Excel 2007 introduced AVERAGEIF which we used to have to work out using similar array formula construct of AVERAGE(IF(....)).

So the formula builds an array of TRUES and FALSES for each IF statement.. and then "blends" them so that TRUES that line up become TRUE and all other combos yield FALSE... then the TRUEs are lined up with the final array to find which lines up with the TRUE(s) and finds the MEDIAN from those.

You can use the Evaluate Formula function found in the Tools|Formula Auditing menu in XL2003 and in the Formulas tab of XL2007 or later. This tool allows you to step through the formula to see what's happening and how you achieve the result you got.

**welchs101** · 10-05-2011, 09:30 AM

very cool! i did not know about this. I have tended to stay away from these "array" type formulas and to be honest i dont remember why.

Is there any other "literature" type stuff on this so i can read it and perhaps apply this to someother issue i may have.......

thanks again.