[FCC(SW)JP USN]

Trying to figure out how to automatically avearge the number of days by the
month they dparted. I could manually do this each month by using the avearge
function for instance =AVERAGE(H849:H880) but how can I set up a formula to
avearge the total days based on the yyyymm of the detach date. Any help would
be great.
F G H
Report date Detach date Total days
878 29-Aug-06 26-Sep-06 27
879 22-Aug-06 27-Sep-06 35
880 12-Sep-06 29-Sep-06 17
881 6-Sep-06 2-Oct-06 26
882 6-Sep-06 2-Oct-06 26
883 8-Aug-06 3-Oct-06 55
884 6-Sep-06 3-Oct-06 27

[Tom Ogilvy]

Assuming everyone in G849 to G880 has a detach date:
= (sum(G849:G880)-sum(H849:H880))/rows(G849:G880)

Format the cell with the formula as General (it might default to looking
like a weird date).

--
Regards,
Tom Ogilvy



"FCC(SW)JP USN" wrote:

Trying to figure out how to automatically avearge the number of days by the
month they dparted. I could manually do this each month by using the avearge
function for instance =AVERAGE(H849:H880) but how can I set up a formula to
avearge the total days based on the yyyymm of the detach date. Any help would
be great.
F G H
Report date Detach date Total days
878 29-Aug-06 26-Sep-06 27
879 22-Aug-06 27-Sep-06 35
880 12-Sep-06 29-Sep-06 17
881 6-Sep-06 2-Oct-06 26
882 6-Sep-06 2-Oct-06 26
883 8-Aug-06 3-Oct-06 55
884 6-Sep-06 3-Oct-06 27

[FCC(SW)JP USN[_2_]]

Hey Tom appreciate the quick response. Everyone in G from G2 through G1000
have a detach date. The detach dates go from 2003-present. On a summry sheet
I have to display the average days onboard by month and year. Column H has
the number of days the individual was here, my end result needs to be
something along the lines of average H2:H1000 if G2:G1000,"yyyymm"=200610 but
I don't quite no how to make it work. I have read many articles, and tried
various Ideas utilizing SUMPRODUCT and Average if, but am not having any
luck.

JP

"Tom Ogilvy" wrote:

Assuming everyone in G849 to G880 has a detach date:
= (sum(G849:G880)-sum(H849:H880))/rows(G849:G880)

Format the cell with the formula as General (it might default to looking
like a weird date).

--
Regards,
Tom Ogilvy



"FCC(SW)JP USN" wrote:

Trying to figure out how to automatically avearge the number of days by the
month they dparted. I could manually do this each month by using the avearge
function for instance =AVERAGE(H849:H880) but how can I set up a formula to
avearge the total days based on the yyyymm of the detach date. Any help would
be great.
F G H
Report date Detach date Total days
878 29-Aug-06 26-Sep-06 27
879 22-Aug-06 27-Sep-06 35
880 12-Sep-06 29-Sep-06 17
881 6-Sep-06 2-Oct-06 26
882 6-Sep-06 2-Oct-06 26
883 8-Aug-06 3-Oct-06 55
884 6-Sep-06 3-Oct-06 27

[Tom Ogilvy]

=sumproduct(--(Month($G$2:$G$1000)=10),--(Year($G$2:$G$1000)=2006),($G$2:$G$1000-$F$2:$F$1000))/sumproduct(--(Month($G$2:$G$1000)=10),--(Year($G$2:$G$1000)=2006))


You can replace the 10 and 2006 with references to cells that contain 10 and
2006 (or other month year combination).

--
Regards,
Tom Ogilvy




"FCC(SW)JP USN" wrote in message
...
Hey Tom appreciate the quick response. Everyone in G from G2 through G1000
have a detach date. The detach dates go from 2003-present. On a summry
sheet
I have to display the average days onboard by month and year. Column H has
the number of days the individual was here, my end result needs to be
something along the lines of average H2:H1000 if G2:G1000,"yyyymm"=200610
but
I don't quite no how to make it work. I have read many articles, and tried
various Ideas utilizing SUMPRODUCT and Average if, but am not having any
luck.

JP

"Tom Ogilvy" wrote:

Assuming everyone in G849 to G880 has a detach date:
= (sum(G849:G880)-sum(H849:H880))/rows(G849:G880)

Format the cell with the formula as General (it might default to looking
like a weird date).

--
Regards,
Tom Ogilvy



"FCC(SW)JP USN" wrote:

Trying to figure out how to automatically avearge the number of days by
the
month they dparted. I could manually do this each month by using the
avearge
function for instance =AVERAGE(H849:H880) but how can I set up a
formula to
avearge the total days based on the yyyymm of the detach date. Any help
would
be great.
F G H
Report date Detach date Total days
878 29-Aug-06 26-Sep-06 27
879 22-Aug-06 27-Sep-06 35
880 12-Sep-06 29-Sep-06 17
881 6-Sep-06 2-Oct-06 26
882 6-Sep-06 2-Oct-06 26
883 8-Aug-06 3-Oct-06 55
884 6-Sep-06 3-Oct-06 27

[FCC(SW)JP USN[_2_]]

Tom,
That worked great and will save time in the future once I get it all set up.
I have one more question there is a slight difference, and I mean very slight
between that formula and what average does when just dealing with those
cells. The formula you gave me comes up with an answer of 43.88, and just
averaging the cells in H using average it comes up with 43.24. No worries but
was just wondering. The numbers I averaged for the Oct 2006 a
26.00
26.00
55.00
27.00
49.00
54.00
23.00
37.00
37.00
30.00
54.00
75.00
62.00
22.00
36.00
75.00
47.00


Again thanks for your time. Much appreciated.

JP

"Tom Ogilvy" wrote:

=sumproduct(--(Month($G$2:$G$1000)=10),--(Year($G$2:$G$1000)=2006),($G$2:$G$1000-$F$2:$F$1000))/sumproduct(--(Month($G$2:$G$1000)=10),--(Year($G$2:$G$1000)=2006))


You can replace the 10 and 2006 with references to cells that contain 10 and
2006 (or other month year combination).

--
Regards,
Tom Ogilvy




"FCC(SW)JP USN" wrote in message
...
Hey Tom appreciate the quick response. Everyone in G from G2 through G1000
have a detach date. The detach dates go from 2003-present. On a summry
sheet
I have to display the average days onboard by month and year. Column H has
the number of days the individual was here, my end result needs to be
something along the lines of average H2:H1000 if G2:G1000,"yyyymm"=200610
but
I don't quite no how to make it work. I have read many articles, and tried
various Ideas utilizing SUMPRODUCT and Average if, but am not having any
luck.

JP

"Tom Ogilvy" wrote:

Assuming everyone in G849 to G880 has a detach date:
= (sum(G849:G880)-sum(H849:H880))/rows(G849:G880)

Format the cell with the formula as General (it might default to looking
like a weird date).

--
Regards,
Tom Ogilvy



"FCC(SW)JP USN" wrote:

Trying to figure out how to automatically avearge the number of days by
the
month they dparted. I could manually do this each month by using the
avearge
function for instance =AVERAGE(H849:H880) but how can I set up a
formula to
avearge the total days based on the yyyymm of the detach date. Any help
would
be great.
F G H
Report date Detach date Total days
878 29-Aug-06 26-Sep-06 27
879 22-Aug-06 27-Sep-06 35
880 12-Sep-06 29-Sep-06 17
881 6-Sep-06 2-Oct-06 26
882 6-Sep-06 2-Oct-06 26
883 8-Aug-06 3-Oct-06 55
884 6-Sep-06 3-Oct-06 27

[Tom Ogilvy]

I set up a table with your numbers and some additional dates in 2006 and
2005. My formula gave me 43.24

I suspect it is picking up an additional row or more somewhere in your data.

It should get exactly the same answer as you would get averaging the
differences.

Only way I could trouble shoot it is having your data. If you want me to
look
send a sample worksheet/workbook to



--
Regards,
Tom Ogilvy



"FCC(SW)JP USN" wrote in message
...
Tom,
That worked great and will save time in the future once I get it all set
up.
I have one more question there is a slight difference, and I mean very
slight
between that formula and what average does when just dealing with those
cells. The formula you gave me comes up with an answer of 43.88, and just
averaging the cells in H using average it comes up with 43.24. No worries
but
was just wondering. The numbers I averaged for the Oct 2006 a
26.00
26.00
55.00
27.00
49.00
54.00
23.00
37.00
37.00
30.00
54.00
75.00
62.00
22.00
36.00
75.00
47.00


Again thanks for your time. Much appreciated.

JP

"Tom Ogilvy" wrote:

=sumproduct(--(Month($G$2:$G$1000)=10),--(Year($G$2:$G$1000)=2006),($G$2:$G$1000-$F$2:$F$1000))/sumproduct(--(Month($G$2:$G$1000)=10),--(Year($G$2:$G$1000)=2006))


You can replace the 10 and 2006 with references to cells that contain 10
and
2006 (or other month year combination).

--
Regards,
Tom Ogilvy




"FCC(SW)JP USN" wrote in message
...
Hey Tom appreciate the quick response. Everyone in G from G2 through
G1000
have a detach date. The detach dates go from 2003-present. On a summry
sheet
I have to display the average days onboard by month and year. Column H
has
the number of days the individual was here, my end result needs to be
something along the lines of average H2:H1000 if
G2:G1000,"yyyymm"=200610
but
I don't quite no how to make it work. I have read many articles, and
tried
various Ideas utilizing SUMPRODUCT and Average if, but am not having
any
luck.

JP

"Tom Ogilvy" wrote:

Assuming everyone in G849 to G880 has a detach date:
= (sum(G849:G880)-sum(H849:H880))/rows(G849:G880)

Format the cell with the formula as General (it might default to
looking
like a weird date).

--
Regards,
Tom Ogilvy



"FCC(SW)JP USN" wrote:

Trying to figure out how to automatically avearge the number of days
by
the
month they dparted. I could manually do this each month by using the
avearge
function for instance =AVERAGE(H849:H880) but how can I set up a
formula to
avearge the total days based on the yyyymm of the detach date. Any
help
would
be great.
F G H
Report date Detach date Total days
878 29-Aug-06 26-Sep-06 27
879 22-Aug-06 27-Sep-06 35
880 12-Sep-06 29-Sep-06 17
881 6-Sep-06 2-Oct-06 26
882 6-Sep-06 2-Oct-06 26
883 8-Aug-06 3-Oct-06 55
884 6-Sep-06 3-Oct-06 27