Deriving Monthly Median Values

John has a huge worksheet that includes daily data covering about sixty years. He wants to come up with a formula that will calculate the median values for each month in the time period covered by the data.

Before proposing a solution, it is best to lay out some assumptions. For the purposes of this tip, let's assume that the daily data is in columns A and B. In column A are the dates associated with the data and in column B are the actual data values for each of those dates. Further, cells A1 and B1 contain headings for each column. This means that the actual data is approximately in the range of A2:B22000.

To make your formulas easier to use, you should define names for the data in both columns A and B. Select the range of dates (for example, A2:A22000) and assign it a name such as AllDates. Select the range of corresponding data (for example, B2:B22000) and use the same process to create a name such as AllData.

You can use array formulas to calculate the actual median values. This involves setting up another data table to contain the medians. Place headings such as "Month" in cell E1 and "Median" in cell F1. In cell E2 place the first day of the first month of your data set, such as 1/1/1940. In cell E3 put a date that is one month later, such as 2/1/1940. Select these two cells (E2:E3) and drag the fill handle downward for the number of months you want in the data table.

If there are no blanks in your sixty years of data, then enter the following formula into cell F2:

=MEDIAN(IF(DATE(YEAR(AllDates),MONTH(AllDates),1)=E2,AllData))

Finalize the formula by pressing Ctrl+Shift+Enter, which tells Excel that this is an array formula. You can then copy the formula in F2 into each of the cells in column F that has a corresponding month in column E. The formula analyzes the dates in column B, and if the year and month are equal to whatever date you put in cell E2, then the median is calculated from all corresponding data points.

If there are blanks in your sixty years of data (a date in column A with no corresponding value in column B), then the formula treats the blank as if it is a zero value. If you do have blanks, this may result in skewed medians. To get around this, you could use a different array formula that checks for and ignores any blank values:

=MEDIAN(IF((DATE(YEAR(AllDates),MONTH(AllDates),1)=E2)*ISNUMBER(AllData),AllData))

There is one caveat to using array formulas in this manner. If you have sixty years of data, with approximately 22,000 individual values, then that is still a lot of months: about 720 of them. That means that you are creating 720 array formulas, each analyzing 22,000 data values to arrive at an answer. That is a lot of calculating going on, so you will notice a slow-down in the responsiveness of Excel whenever it recalculates the worksheet.

If the sluggishness becomes an issue, then you could look at reorganizing your original data so that each row of the worksheet represents an individual month. Column A could contain the month for the row (1/1/1940, 2/1/1940, 3/1/1940, etc.) and the columns B:AF would be days 1 through 31 for each month. The intersecting cells in the table could then contain the data point for each day in the month, and you could use the MEDIAN function in column AG to calculate the median for each month. This still results in 720 formulas, but these are regular formulas that each only need to process a month's worth of data in stead of the array formulas that need to each process sixty years of data. The result is much faster calculations.

Of course, for most people the idea of reorganizing such a huge amount of data is enough to keep you awake at night. Instead, you can take an entirely different approach to analyzing the data. This approach is possible because a median is a very easy statistical function to manually calculate. You simply sort your data set and, if the number of items in the data set is odd, select the middle item. If the number of items is even, then you take the average of the two middle items.

To get ready to analyze the data, there are a couple of things to do. First, it will be handy to have some way to uniquely identify the month of each data point. In cell C2 place the following formula:

=100*Year(A2)+Month(A2) 

This results in a values such as 194001, 194002, 194003, etc. being stored in column C. This is the unique month value. Now, you should sort the data by column C and then by column B. Both sorts should be in ascending order, so that you end up with your data first sorted by year/month and then by value within the year/month.

Next you need to add subtotals to your data. Choose Subtotals from the Data menu, which displays the Subtotal dialog box. You want to add a subtotal at each change in column C. The function to use is Count, and you want to add the subtotal to column B. When you click OK, you end up with 720 subtotals, one for each month in the data range, each one showing a count of how many data items there were in that month.

To get the medians, add a formula to cell D2:

IF(RIGHT(B2,5)="Count", IF(MOD(C2,2)=1, INDIRECT("B"&(ROW()-1)-C2/2+1/2), (INDIRECT("B"&(ROW()-1)-C2/2)+INDIRECT("B"&(ROW()-1)-C2/2+1))/2), "")

The formula examines what is in cell B2, and if it contains the word "Count," then it knows that this is a subtotal row. In that case, it checks to see whether the count in cell C2 is odd or even. If it is odd, then the INDIRECT function is used to grab whatever the median value is for the month. If it is even, then the two middle values for the month are added together and divided in half.

The result is that you now have median values for each month, in the same row as your subtotals. Thus, you can collapse the outline for the data (click the plus signs in the outline area at the left of your data) so that you only show those subtotal rows.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3086) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Deriving Monthly Median Values.