How to calculate median per month?

[alexjust]

Excel.JPG

I need to calculate the median time it takes to complete a project in one given month.

I attached a sample table, which is for reference purposes. The actual table will have more entries per month. In "Month, Year" (where you see ???) cell I need the result to show the medium for this particular month. The column A is not sorted by date so I need for Excel to recognize the month as opposed to just me indicating the range the median should be derived from.

Any help would be greatly appreciated.

P.S. A special thanks to those who could also write a formula to calculate January In some of my other formulas I had issues with calculating data from January.
I've done some research on the net, but couldn't find the right formula.

[Kaper]

For all data described just for month (do not care of year)
Use an array formula (committed with Control+Shift+Enter) similar to this one:

Please Login or Register  to view this content.

Here I made assumption that the month is determined by start date (in column A) and duration is in column.
The result will be for January,
copy down for next 11 rows


If you want take into account also a year - instead of simple condition on monthnumber, more elaborated shall be used to check both year and month.
But as there is no attachment with sample spreadsheet I cease to do it.

[alexjust]

For all data described just for month (do not care of year)
Use an array formula (committed with Control+Shift+Enter) similar to this one:

Please Login or Register  to view this content.

Here I made assumption that the month is determined by start date (in column A) and duration is in column.
The result will be for January,
copy down for next 11 rows


If you want take into account also a year - instead of simple condition on monthnumber, more elaborated shall be used to check both year and month.
But as there is no attachment with sample spreadsheet I cease to do it.

Thanks for the quick response. The formula give 0 in the result for some reason. What does ROW(1:1) mean and what is it for? I need Excel to calculate all entries in Column A, which have a specific month. Btw, some results in Column C (number of days it takes to complete a project) may be missing. Will that affect the results or MEDIAN function will ignore blanks?

[Kaper]

ROW(1:1) is row number for the row1 - so it is 1.
Once copied down it will become 2,3,4,... and so on in next cells.

Result 0 - you probably entered the formula as regular one (commited with Enter) while it is an array formula - I tried to emphasize that.

My frank suggestion: Read and follow the forum rules http://www.excelforum.com/forum-rule...rum-rules.html

Personal special recommendations:

rule 12. Don't quote whole posts -- it's just clutter.

below rule 13 there is a section
Want to get your question answered quickly?
and in it:

Post a WORKBOOK. Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

Best Regards,