Need to Sum Mon-Sat each seven days; Average Sunday

[Robert Moore]

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.

 

[Bob Phillips]

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]

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]

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)

 

[Domenic]

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!

 

[Roger Govier]

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]

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.

 

[Robert Moore]

Thank you, Domenic's formula works best...

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!