Mode If and Median If help

[bobthebofifn]

Hello.

I have two sets of dates in columns AN (Start) and AP (End) running from September onwards. I would like to find the mode and median number of days it took to complete (from Start date to Finish date) for each month.

Is this possible?

If not, I have already got this formula next to the two columns

=IF(AN2="","",AN2-AP2)

(as the spread sheet is Dynamic) so how could I go about finding, for example, the mode number of days it took to complete during September, during October etc?

Many thanks in advance for your help.

[ChemistB]

So, if a project starts (AN2) in Sept but ends (AP2) in Oct, would you use Oct or Sept for calculating your mode and median values?

[bobthebofifn]

Um, it would be the difference between the end date and the start date. e.g.

Start 18/11/14 End 20/11/14 End - Start = 2
Start 19/11/14 End 20/11/14 End - Start = 1

[ChemistB]

You misunderstood my question

Start 29/11/14 End 02/12/14 End - Start = 3 BUT would you use that data for median of November or Median of December?

[bobthebofifn]

Oh right, sorry, the end date

[ChemistB]

With end date in AP, Difference in AQ, and where E3 contains "November", use this ARRAYED formula

=MODE(IF(TEXT($AP$2:$AP$30,"mmmm")=E$3,$AQ$2:$AQ$30))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.