Getting the Weekly Daily Average

[Jeff Jensen]

I've been working on this since Monday and it's driving me nuts. Here's what
I've got going:

Cells C1:IU1 lists all the workdays (excluding holidays) for the year 2009
as follows:
C=1/5, D=1/6, E=1/7, F=1/8, G=1/9, H=1/12, I=1/13, J=1/14, etc., etc.

Cells C2:IU2 contains numeric data that is entered each day such as:
C=164, D=230, E=86, F=205, G=198, H=175, I=201, J=165

My problem is in cells C3:IU3 I need the "Weekly Daily Average". I'm
currently doing it this way (which works):
C3=C2, D3=AVERAGE(C$22), E3=AVERAGE(C$2:E2), F3=AVERAGE(C$2:F2),
G3=AVERAGE(C$2:G2), H3=H2, I3=AVERAGE(H$2:I2), J3=AVERAGE(H$2:J2), and so
on. The results (if using the numeric data I provided) should be: C3=164,
D3=197, E3=160, F3=171, G3=177, H3=175, I3=188, J3=180,

The problems with doing it this way is that I can't finish formatting the
sheet until the owner figures out the holiday schedule and I have to keep
changing the formulas each year because the beginning of each week ends up in
different cells.

I tried to see if I could come up with something by using a helper row to
reference in cells C4:IU4 for some way of distinguishing the beginning thru
the end of each week by using the WEEKNUM function as follows:
C4=WEEKNUM(C1), D4=WEEKNUM(D1), E4=WEEKNUM(E1), etc., etc.. It returns this:
C4=2, D4=2, E4=2, F4=2, G4=2, H4=3, I4=3, J4=3, etc., etc.
Anyway, I still can't figure out what to do even using the helpers.

Is there a way to accomplish this?

P.S. I am using Excel 2003

Thank you,

Jeff Jensen

 

[Luke M]

Had to rearrange your layout, but I believe the formula (sorta) works.
Using column A for all dates in a year (365 days), start at row 8. Column B
is your numbers. In Column F is a list of holiday dates.

In C8
=IF(OR(WEEKDAY(A8,2)>5,ISNUMBER(MATCH(A8,$F$8:$F$35,0))),0,SUM(OFFSET(B8,-WEEKDAY(A8,2)+1,0):OFFSET(B8,5-WEEKDAY(A8,2),0))/COUNTIF(OFFSET(B8,-WEEKDAY(A8,2)+1,0):OFFSET(B8,5-WEEKDAY(A8,2),0),">0"))

If date is a holiday, or has no data entered, it is not counted. Offset
formulas allow for the 1 formula to be copied all the way down. Advantages
are that you holidays can be easily added/subtracted, and change of year
won't mess up formulas.

 

[Jeff Jensen]

Hi Luke,

You're right, it does "sorta" work--it's better that anything I've come up
with so far. There is still a problem:
Your way only gets the "Weekly" average (not too bad), but what I realy need
is the "Weekly Daily Average"(the average through out the week), for example:

Your Way
Date..: 1/5, 1/6, 1/7, 1/8, 1/9, 1/12, 1/13, 1,14
Data..: 164, 230, 86, 205, 198, 175, 201, 165
Result: 177, 177, 177, 177, 177, 180, 180, 180

What I Need
Date..: 1/5, 1/6, 1/7, 1/8, 1/9, 1/12, 1/13, 1,14
Data..: 164, 230, 86, 205, 198, 175, 201, 165
Result: 164, 197, 160, 171, 177, 175, 188, 180

I hope we can figure this out.

Thank you for your help.

Jeff