Need to Sum Mon-Sat each seven days; Average Sunday
I have a spreadsheet where numbers are entered or calculated in columns
representing each day of the year. The column headers are 1/1/05 1/2/05 etc. The second row in each column returns the appropriate day of the week: Mon Tue Wed, etc corresponding to the date. Then numbers or calculations returning numbers in each row thereafter. 10/31/05 11/1/05 11/2/05 11/3/05 11/4/05 etc. Mon Tue Wed Thu Fri 5 3 5 0 1 I then need to create a report that creates two columns, one with the total (or average) of the Monday through Saturday numbers and one for the Sunday numbers. In other words a Daily calculation and a Sunday calculation. How would I create this report which goes to the daily worksheet and grabs the Mon-Sat numbers for week 1, the Sunday number for week 1, then goes to week 2, week 3, etc. |
Need to Sum Mon-Sat each seven days; Average Sunday
Robert,
If I understand correctly you want =SUM(OFFSET($A$3,0,(COLUMN(A1)-1)*7,20,6)) and =SUM(OFFSET($A$3,0,(COLUMN(A2)-1)*7+6,20,1)) This assumes that the totals are going across a single row. If it will be down a column change COLUMN(A1) to ROW(A1) -- HTH RP (remove nothere from the email address if mailing direct) "Robert Moore" wrote in message .net... I have a spreadsheet where numbers are entered or calculated in columns representing each day of the year. The column headers are 1/1/05 1/2/05 etc. The second row in each column returns the appropriate day of the week: Mon Tue Wed, etc corresponding to the date. Then numbers or calculations returning numbers in each row thereafter. 10/31/05 11/1/05 11/2/05 11/3/05 11/4/05 etc. Mon Tue Wed Thu Fri 5 3 5 0 1 I then need to create a report that creates two columns, one with the total (or average) of the Monday through Saturday numbers and one for the Sunday numbers. In other words a Daily calculation and a Sunday calculation. How would I create this report which goes to the daily worksheet and grabs the Mon-Sat numbers for week 1, the Sunday number for week 1, then goes to week 2, week 3, etc. |
Need to Sum Mon-Sat each seven days; Average Sunday
This would be an example of Week 1, The next seven columns would be
Week 2. Mon Tue Wed Thu Fri Sat Sun SAM 1 2 3 4 5 6 7 BUTCH 2 3 4 5 6 7 8 ELMER 3 4 5 6 7 8 9 My report for containing each week would look like this: WEEK 1 WEEK 2 DAILY SUNDAY DAILY SUNDAY 21 7 27 8 33 9 I would like to create the above report, and fill in the formula for all 52 weeks so that as each week is populated I will create the above report. Assume the cell "SAM" is A3. Your formulas may work fine but it's not clear to me how to utilize them to create what I need. In other words, a formula that looks at Mon-Sat of Week 1, then Week 2, then Week 3, Then a second formula that brings in Sunday of Week 1, then Week 2, etc. Bob Phillips wrote: Robert, If I understand correctly you want =SUM(OFFSET($A$3,0,(COLUMN(A1)-1)*7,20,6)) and =SUM(OFFSET($A$3,0,(COLUMN(A2)-1)*7+6,20,1)) This assumes that the totals are going across a single row. If it will be down a column change COLUMN(A1) to ROW(A1) |
Need to Sum Mon-Sat each seven days; Average Sunday
Did you try my formula? It needs amending to start at B2 not A3, and the 20
should be changed to the correct number of rows. If you did try it, what was wrong with it? -- HTH RP (remove nothere from the email address if mailing direct) "Robert Moore" wrote in message nk.net... This would be an example of Week 1, The next seven columns would be Week 2. Mon Tue Wed Thu Fri Sat Sun SAM 1 2 3 4 5 6 7 BUTCH 2 3 4 5 6 7 8 ELMER 3 4 5 6 7 8 9 My report for containing each week would look like this: WEEK 1 WEEK 2 DAILY SUNDAY DAILY SUNDAY 21 7 27 8 33 9 I would like to create the above report, and fill in the formula for all 52 weeks so that as each week is populated I will create the above report. Assume the cell "SAM" is A3. Your formulas may work fine but it's not clear to me how to utilize them to create what I need. In other words, a formula that looks at Mon-Sat of Week 1, then Week 2, then Week 3, Then a second formula that brings in Sunday of Week 1, then Week 2, etc. Bob Phillips wrote: Robert, If I understand correctly you want =SUM(OFFSET($A$3,0,(COLUMN(A1)-1)*7,20,6)) and =SUM(OFFSET($A$3,0,(COLUMN(A2)-1)*7+6,20,1)) This assumes that the totals are going across a single row. If it will be down a column change COLUMN(A1) to ROW(A1) |
Need to Sum Mon-Sat each seven days; Average Sunday
Assuming that your starting points for Week 1, Daily and Sunday, are A10
and B10, try the following... For Week 1, Daily... A10, copied down: =SUM(OFFSET($B3,,(COLUMN(A10)-COLUMN($A10)+2)/2*7-7,,6)) For Week 1, Sunday... B10, copied down: =SUM(OFFSET($B3,,(COLUMN(B10)-COLUMN($B10)+2)/2*7-1)) Then for other weeks, copy A10 to Columns C, E, G, etc., and B10 to Columns D, F, H, etc. Adjust the references accordingly. Hope this helps! In article et, Robert Moore wrote: This would be an example of Week 1, The next seven columns would be Week 2. Mon Tue Wed Thu Fri Sat Sun SAM 1 2 3 4 5 6 7 BUTCH 2 3 4 5 6 7 8 ELMER 3 4 5 6 7 8 9 My report for containing each week would look like this: WEEK 1 WEEK 2 DAILY SUNDAY DAILY SUNDAY 21 7 27 8 33 9 I would like to create the above report, and fill in the formula for all 52 weeks so that as each week is populated I will create the above report. Assume the cell "SAM" is A3. Your formulas may work fine but it's not clear to me how to utilize them to create what I need. In other words, a formula that looks at Mon-Sat of Week 1, then Week 2, then Week 3, Then a second formula that brings in Sunday of Week 1, then Week 2, etc. |
Need to Sum Mon-Sat each seven days; Average Sunday
Excellent Domenic!
But just copying the respective formulae to A10 and B10, then marking both cells and dragging the fill handle on B10 across the page achieves the desired result. Then mark the range of row 10 and copy down. I don't see how the OP can achieve his 52 weeks going across the page though as he would obviously need 367 columns. Regards Roger Govier Domenic wrote: Assuming that your starting points for Week 1, Daily and Sunday, are A10 and B10, try the following... For Week 1, Daily... A10, copied down: =SUM(OFFSET($B3,,(COLUMN(A10)-COLUMN($A10)+2)/2*7-7,,6)) For Week 1, Sunday... B10, copied down: =SUM(OFFSET($B3,,(COLUMN(B10)-COLUMN($B10)+2)/2*7-1)) Then for other weeks, copy A10 to Columns C, E, G, etc., and B10 to Columns D, F, H, etc. Adjust the references accordingly. Hope this helps! In article et, Robert Moore wrote: This would be an example of Week 1, The next seven columns would be Week 2. Mon Tue Wed Thu Fri Sat Sun SAM 1 2 3 4 5 6 7 BUTCH 2 3 4 5 6 7 8 ELMER 3 4 5 6 7 8 9 My report for containing each week would look like this: WEEK 1 WEEK 2 DAILY SUNDAY DAILY SUNDAY 21 7 27 8 33 9 I would like to create the above report, and fill in the formula for all 52 weeks so that as each week is populated I will create the above report. Assume the cell "SAM" is A3. Your formulas may work fine but it's not clear to me how to utilize them to create what I need. In other words, a formula that looks at Mon-Sat of Week 1, then Week 2, then Week 3, Then a second formula that brings in Sunday of Week 1, then Week 2, etc. |
Need to Sum Mon-Sat each seven days; Average Sunday
In article ,
Roger Govier wrote: But just copying the respective formulae to A10 and B10, then marking both cells and dragging the fill handle on B10 across the page achieves the desired result. Then mark the range of row 10 and copy down. Thanks for pointing that out, Roger! I don't see how the OP can achieve his 52 weeks going across the page though as he would obviously need 367 columns. Actually, I completely overlooked this point. It looks like the OP will have to split it up. However, it's possible that he was already aware of it. If not, he's aware of it now. :) |
Need to Sum Mon-Sat each seven days; Average Sunday
Thank you, Domenic's formula works best...
Domenic wrote: Assuming that your starting points for Week 1, Daily and Sunday, are A10 and B10, try the following... For Week 1, Daily... A10, copied down: =SUM(OFFSET($B3,,(COLUMN(A10)-COLUMN($A10)+2)/2*7-7,,6)) For Week 1, Sunday... B10, copied down: =SUM(OFFSET($B3,,(COLUMN(B10)-COLUMN($B10)+2)/2*7-1)) Then for other weeks, copy A10 to Columns C, E, G, etc., and B10 to Columns D, F, H, etc. Adjust the references accordingly. Hope this helps! In article et, Robert Moore wrote: This would be an example of Week 1, The next seven columns would be Week 2. Mon Tue Wed Thu Fri Sat Sun SAM 1 2 3 4 5 6 7 BUTCH 2 3 4 5 6 7 8 ELMER 3 4 5 6 7 8 9 My report for containing each week would look like this: WEEK 1 WEEK 2 DAILY SUNDAY DAILY SUNDAY 21 7 27 8 33 9 I would like to create the above report, and fill in the formula for all 52 weeks so that as each week is populated I will create the above report. Assume the cell "SAM" is A3. Your formulas may work fine but it's not clear to me how to utilize them to create what I need. In other words, a formula that looks at Mon-Sat of Week 1, then Week 2, then Week 3, Then a second formula that brings in Sunday of Week 1, then Week 2, etc. |
All times are GMT +1. The time now is 11:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com