Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Deriving Monthly Median Values.

Deriving Monthly Median Values

Written by Allen Wyatt (last updated August 7, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


1

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 instead 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 value 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 (12727) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Deriving Monthly Median Values.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Inserting a Copyright Mark

One of the most common symbols that can be added to a document is the copyright mark. This tip examines several ways you ...

Discover More

Saving a Workbook with a Preview

When you save your workbooks, Excel can also save a preview image (thumbnail) that can be displayed in the Open dialog ...

Discover More

Replacing Multiple Spaces with Tabs

If you get a document or some text that has multiple consecutive spaces used to align information, you'll undoubtedly be ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Relative References to Cells in Other Workbooks

When you construct a formula and click on a cell in a different workbook, an absolute reference to that cell is placed in ...

Discover More

Displaying a Value and a Percentage in a Single Cell

Sometimes it can be helpful to show both a numeric value and a percentage in the same cell. This can be done through ...

Discover More

First Value Less Than or Equal to 100

If you need to evaluate a row of values to meet specific criteria, then you'll appreciate the discussion in this tip. It ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 2 + 8?

2019-04-15 11:09:21

David Robinson

I thought I'd share a way to convert column data (dates in column A, data in column B) into the matrix form described straight after "if the sluggishness becomes an issue". Here, the first of each month is in column D and the days in the month, 1 to 31, are in row 1.

In A1 notation:
=IF(E$1+$D2-1 < EDATE($D2,1), SUMIFS($B:$B,$A:$A,E$1+$D2-1), "")

This works by adding the day-in-month to the first-of-month (and then deducting 1 of course, because adding day 1 to the first of May gives the 2nd when you still want the 1st). First we check this gives a date that is still in the month - i.e. that we haven't ended up with thinking we've got a 30th day in February. Then we get the value that matches the criterion - I use SUMIFS because it's quick to write, or you could use INDEX/MATCH etc.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.