Averageing monthly data by type
 

Geez! I'm probably being a dunce, but I cannot figure out how to get some
averages from my data sheet. What I need is the average # of hours per month
of each worker type per shift. Here is my data example:

Month/Day Worker type 08-1600 1600-2400 2400-0800
1-Apr B 5 4
2
C 7 3
2
D 12 8
4
2-Apr B 8 4
2
C 10 8
4





--
Mudbug

Averageing monthly data by type
 

Hi,

Set up a summary area like the following:
08-1600 1600-2400 2400-0800
B 6.5 4 2
C 8.5 5.5 3
D 12 8 4

Assume your original data starts in cell A1:E6 with dates in column A titles
on row 1.
Assume that the above summary area occupies the range G1:J4, enter the
following formula in H2 and copy it down and over:

=AVERAGE(IF((H$1=$C$1:$E$1)*($G2=$B$2:$B$6)*$C$2:$ E$6<0,(H$1=$C$1:$E$1)*($G2=$B$2:$B$6)*$C$2:$E$6," "))

This is an array formula so you need to press Shift+Ctrl+Enter when you
enter it not Enter.


If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"Mudbug" wrote:

Geez! I'm probably being a dunce, but I cannot figure out how to get some
averages from my data sheet. What I need is the average # of hours per month
of each worker type per shift. Here is my data example:

Month/Day Worker type 08-1600 1600-2400 2400-0800
1-Apr B 5 4
2
C 7 3
2
D 12 8
4
2-Apr B 8 4
2
C 10 8
4





--
Mudbug

Averageing monthly data by type
 

Thank you Shane. I think this will work except my first row of data (the 6.5
figure) is in B2. It looks like I need the range of B2:G4 What do I need to
change in the formula you gave me?

Mudbug

"ShaneDevenshire" wrote:

Hi,

Set up a summary area like the following:
08-1600 1600-2400 2400-0800
B 6.5 4 2
C 8.5 5.5 3
D 12 8 4

Assume your original data starts in cell A1:E6 with dates in column A titles
on row 1.
Assume that the above summary area occupies the range G1:J4, enter the
following formula in H2 and copy it down and over:

=AVERAGE(IF((H$1=$C$1:$E$1)*($G2=$B$2:$B$6)*$C$2:$ E$6<0,(H$1=$C$1:$E$1)*($G2=$B$2:$B$6)*$C$2:$E$6," "))

This is an array formula so you need to press Shift+Ctrl+Enter when you
enter it not Enter.


If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"Mudbug" wrote:

Geez! I'm probably being a dunce, but I cannot figure out how to get some
averages from my data sheet. What I need is the average # of hours per month
of each worker type per shift. Here is my data example:

Month/Day Worker type 08-1600 1600-2400 2400-0800
1-Apr B 5 4
2
C 7 3
2
D 12 8
4
2-Apr B 8 4
2
C 10 8
4





--
Mudbug