Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return an average date
I'm trying to do the following:
Col E Col H 9/17/08 in 9/18/08 ti 9/19/08 ot When H13:H36 = in and H13:H36 = ti, Then return the average date from E13:E36. Thanks in advance for any help. Christy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return an average date
This statement
"When H13:H36 = in and H13:H36 = ti" can never be true, if you meant OR as opposed to AND =AVERAGE(IF((B1:B3="in")+(B1:B3="ti"),A1:A3)) entered with ctrl + shift & enter will return 09/17/2008 using your example because there are no half days etc, you would need to involve time and then it will be 9/17/2008 12:00 but if you have many entries with "in" or "ti" it will be strange. Maybe you should explain what you are trying to do? -- Regards, Peo Sjoblom "Christy" wrote in message ... I'm trying to do the following: Col E Col H 9/17/08 in 9/18/08 ti 9/19/08 ot When H13:H36 = in and H13:H36 = ti, Then return the average date from E13:E36. Thanks in advance for any help. Christy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return an average date
=INT(AVERAGE(IF((H13:H36={"in","ti"}),E13:E36)))
ctrl+shift+enter, not just enter "Christy" wrote: I'm trying to do the following: Col E Col H 9/17/08 in 9/18/08 ti 9/19/08 ot When H13:H36 = in and H13:H36 = ti, Then return the average date from E13:E36. Thanks in advance for any help. Christy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return an average date
Thank you, thank you and thank you. It worked!
"Teethless mama" wrote: =INT(AVERAGE(IF((H13:H36={"in","ti"}),E13:E36))) ctrl+shift+enter, not just enter "Christy" wrote: I'm trying to do the following: Col E Col H 9/17/08 in 9/18/08 ti 9/19/08 ot When H13:H36 = in and H13:H36 = ti, Then return the average date from E13:E36. Thanks in advance for any help. Christy |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return an average date
=INT(AVERAGE(IF((H13:H36={"in","ti"}),E13:E36)))
Using ROUND would be more accurate. -- Biff Microsoft Excel MVP "Teethless mama" wrote in message ... =INT(AVERAGE(IF((H13:H36={"in","ti"}),E13:E36))) ctrl+shift+enter, not just enter "Christy" wrote: I'm trying to do the following: Col E Col H 9/17/08 in 9/18/08 ti 9/19/08 ot When H13:H36 = in and H13:H36 = ti, Then return the average date from E13:E36. Thanks in advance for any help. Christy |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return an average date
How would I alter this using ROUND? Also, when cells are blank I would like
it if the cell that contained this formula didn't say #DID/O! but was just blank. Thanks. "T. Valko" wrote: =INT(AVERAGE(IF((H13:H36={"in","ti"}),E13:E36))) Using ROUND would be more accurate. -- Biff Microsoft Excel MVP "Teethless mama" wrote in message ... =INT(AVERAGE(IF((H13:H36={"in","ti"}),E13:E36))) ctrl+shift+enter, not just enter "Christy" wrote: I'm trying to do the following: Col E Col H 9/17/08 in 9/18/08 ti 9/19/08 ot When H13:H36 = in and H13:H36 = ti, Then return the average date from E13:E36. Thanks in advance for any help. Christy |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return an average date
If you're getting #DIV/0! then that means there are no entries in column H
that meet the criteria. Try this array formula** : =IF(SUM(COUNTIF(H13:H36,{"in","ti"})),ROUND(AVERAG E(IF(H13:H36={"in","ti"},E13:E36)),0),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) If there are no dates in column E then the result will be 0 (formatted as date will display as 1/0/1900) -- Biff Microsoft Excel MVP "Christy" wrote in message ... How would I alter this using ROUND? Also, when cells are blank I would like it if the cell that contained this formula didn't say #DID/O! but was just blank. Thanks. "T. Valko" wrote: =INT(AVERAGE(IF((H13:H36={"in","ti"}),E13:E36))) Using ROUND would be more accurate. -- Biff Microsoft Excel MVP "Teethless mama" wrote in message ... =INT(AVERAGE(IF((H13:H36={"in","ti"}),E13:E36))) ctrl+shift+enter, not just enter "Christy" wrote: I'm trying to do the following: Col E Col H 9/17/08 in 9/18/08 ti 9/19/08 ot When H13:H36 = in and H13:H36 = ti, Then return the average date from E13:E36. Thanks in advance for any help. Christy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to return the average of the LAST 3 numbers in a row | Excel Worksheet Functions | |||
Date Function formula that will return the date of a specific week | Excel Worksheet Functions | |||
Array Formula to find Average Return | Excel Discussion (Misc queries) | |||
AVERAGE Row of Numbers and Return Corresponding Numeric Label | Excel Worksheet Functions | |||
average annual return | Excel Worksheet Functions |