Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting the Weekly Daily Average
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$2:D2), 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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting the Weekly Daily Average
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$3 5,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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Jeff Jensen" wrote: 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$2:D2), 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting the Weekly Daily Average
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 "Luke M" wrote: 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$3 5,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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Jeff Jensen" wrote: 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$2:D2), 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting daily data into weekly | Excel Discussion (Misc queries) | |||
Converting Daily Info to Weekly | Excel Discussion (Misc queries) | |||
average daily counts to weekly counts | Excel Discussion (Misc queries) | |||
Weekly bar charts assumes i mean daily bar chart | Charts and Charting in Excel | |||
formatting daily,weekly and monthly schedules | New Users to Excel |