Averageing monthly data by type

M

Mudbug

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
 
S

ShaneDevenshire

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.
 
M

Mudbug

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top