Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Functions & Formulas
Did you ever get an answer? I have been looking for this for years also but
also need to break down the average into quarterly averages. I have been given the following formulas to try =AVERAGE(IF(INT(MONTH(C:C)+2/3)=4 but they don't compute correctly for some strange reason. If you did find a solution, please I still need to know. Thank you Kathi "Susie D" wrote: Hello I am working an a s/sheet which amongst other things shows 2 dates, the first one is the date a call was opened & the second one is a date the call was closed. I have to calculate the number of days each call was open for (excluding weekends) to get my average. Now, I could do this individually but there are approx 5000 individual calls so I would really appreciate it if anyone could help with this one? -- Susie D |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Functions & Formulas
Hi Kathi
I think you have only posted part of the formula. There must be some other values to be averaged, if the date happens to fall in the 4th quarter. Maybe something like the following array entered formula {=AVERAGE(IF(INT((MONTH(C1:C1000)+2)/3)=4,D1:D1000))} Not this is an array formula, so it must be committed or edited using Ctrl+Shift+Enter (CSE) not just Enter. When you use CSE, Excel will insert the curly braces { } around the whole formula. Do not type them yourself. You cannot use whole columns for this formula, unless every cell is filled, otherwise you will get a #NUM error. Suzie Once again, I am not seeing the whole of this thread so don't know whether you have already had an answer. With Open date in A1 and Close date in B1, enter in C1 =NETWORKDAYS(A1,B1) then carry out your average of the data in column C. If you want to exclude holiday dates as well as weekends, then use the optional additional parameter =NETWORKDAYS(A1,B1,holidays) where holidays can either be a named range containing public holiday dates, or a range of cells e.g. $G$1:$G$9 which hold the holiday dates. -- Regards Roger Govier "kathi" wrote in message ... Did you ever get an answer? I have been looking for this for years also but also need to break down the average into quarterly averages. I have been given the following formulas to try =AVERAGE(IF(INT(MONTH(C:C)+2/3)=4 but they don't compute correctly for some strange reason. If you did find a solution, please I still need to know. Thank you Kathi "Susie D" wrote: Hello I am working an a s/sheet which amongst other things shows 2 dates, the first one is the date a call was opened & the second one is a date the call was closed. I have to calculate the number of days each call was open for (excluding weekends) to get my average. Now, I could do this individually but there are approx 5000 individual calls so I would really appreciate it if anyone could help with this one? -- Susie D |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Functions & Formulas
"Roger Govier" wrote: Hi Kathi I think you have only posted part of the formula. There must be some other values to be averaged, if the date happens to fall in the 4th quarter. Maybe something like the following array entered formula {=AVERAGE(IF(INT((MONTH(C1:C1000)+2)/3)=4,D1:D1000))} Not this is an array formula, so it must be committed or edited using Ctrl+Shift+Enter (CSE) not just Enter. When you use CSE, Excel will insert the curly braces { } around the whole formula. Do not type them yourself. You cannot use whole columns for this formula, unless every cell is filled, otherwise you will get a #NUM error. Suzie Once again, I am not seeing the whole of this thread so don't know whether you have already had an answer. With Open date in A1 and Close date in B1, enter in C1 =NETWORKDAYS(A1,B1) then carry out your average of the data in column C. If you want to exclude holiday dates as well as weekends, then use the optional additional parameter =NETWORKDAYS(A1,B1,holidays) where holidays can either be a named range containing public holiday dates, or a range of cells e.g. $G$1:$G$9 which hold the holiday dates. -- Regards Roger Govier "kathi" wrote in message ... Did you ever get an answer? I have been looking for this for years also but also need to break down the average into quarterly averages. I have been given the following formulas to try =AVERAGE(IF(INT(MONTH(C:C)+2/3)=4 but they don't compute correctly for some strange reason. If you did find a solution, please I still need to know. Thank you Kathi "Susie D" wrote: Hello I am working an a s/sheet which amongst other things shows 2 dates, the first one is the date a call was opened & the second one is a date the call was closed. I have to calculate the number of days each call was open for (excluding weekends) to get my average. Now, I could do this individually but there are approx 5000 individual calls so I would really appreciate it if anyone could help with this one? -- Susie D |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with Excel Formulas or Functions | Excel Discussion (Misc queries) | |||
wild cards in formulas and functions | Excel Worksheet Functions | |||
Improvements for text finding functions | Excel Discussion (Misc queries) | |||
Help, Urgent Excel Formulas are not calculating | Excel Discussion (Misc queries) | |||
OLAP write back via formulas (functions), not read only pivot tab | Excel Worksheet Functions |