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

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

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