mean of range of dates

[leot]

I have a column with a range of dates.
4/2/2006
4/4/2006
4/4/2007
4/7/2008
4/9/2006
5/1/2007
5/2/2006
5/2/2007
5/3/2008

How can I get a mean date for the range by month. Meaning ignoring the
year. Thanks for any suggestions.

 

[Stefi]

Try this way:
Dates being in column A Enter formula
=DATE(2008,MONTH(A2),DAY(A2))
in B2, fill it down and
Enter formula
=AVERAGE(B2:B10)
in the next cell in Column B!
Adjust Ranges!

Regards,
Stefi


„leot†ezt írta:

 

[Roger Govier]

Hi

I placed the month required, 4, in cell D1 then used this array entered
formula
{=AVERAGE(IF(MONTH($A$1:$A$8)=$D1,$A$1:$A$8))}
Format the cell with the formula as Date

To enter or modify an Array formula, use Control+Shift+Enter (CSE) not just
Enter.
Do not type the curly braces { } yourself. If you use CSE, Excel will
automatically insert them.

 

[Jarek Kujawa]

while Roger's formula is the most transparent you might also try:

=SUMPRODUCT((MONTH($A$1:$A$8)=4)*($A$1:$A$8))/SUMPRODUCT((MONTH($A$1:$A
$8)=4)*1)


=SUM(IF(MONTH($A$1:$A$8)=4,$A$1:$A$8,))/SUM(IF(MONTH($A$1:$A$8)=4,1))
(CTRL+SHIFT+ENTER this formula as it is an array formula)

HIH