| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
mean of range of dates
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
mean of range of dates
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: 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
mean of range of dates
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. -- Regards Roger Govier "leot" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
mean of range of dates
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 On 15 Kwi, 13:06, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: 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. -- Regards Roger Govier "leot" wrote in message ... 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.- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Identifying unique dates in a range of cells containing dates... | Excel Discussion (Misc queries) | |||
| Identifying unique dates within a range of cells containing dates | Excel Discussion (Misc queries) | |||
| How do I find the earliest dates in a range of dates? | Excel Worksheet Functions | |||
| Find dates in a range; then sum values in that range by a criteria | Excel Discussion (Misc queries) | |||
| How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
Hybrid Mode
