Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting daily data into weekly, monthly and yearly data | Excel Discussion (Misc queries) | |||
AVERAGEing non contiguous rows | Excel Discussion (Misc queries) | |||
Averageing Costs | Excel Discussion (Misc queries) | |||
How do I set up monthly random work schedule for 60 hours monthly | Excel Discussion (Misc queries) | |||
Mortgage template comparing interest pd, monthly, bi-monthly, ext. | Excel Discussion (Misc queries) |