quarterly reports

[Al Vanderhoof]

I have a workbook that has 52 weeks(rows) of income and expenses(columns).
I'm trying to set up a quarterly reports sheet that updates from this info,
but I cannot figure out how to set up the formulas that will find the dates
that are in the range and save the subtotals and totals.
Thanks ahead of time for any help

 

[Guest]

Hi Al,
I wsa trying to set up a sheet that I htought might look like yours and came
up with this, although I am sure it is much simpler than what you are really
dealing with:

Date "Income
Weeks" Exp1 Exp2 Net
1/7/2005 1 Exp1 Exp2 Net1
1/14/2005 2 Exp1 Exp2 Net2
1/21/2005 3 Exp1 Exp2 Net3
1/28/2005 4 Exp1 Exp2 Net4
2/4/2005 5 Exp1 Exp2 Net5
2/11/2005 6 Exp1 Exp2 Net6
2/18/2005 7 Exp1 Exp2 Net7
2/25/2005 8 Exp1 Exp2 Net8
3/4/2005 9 Exp1 Exp2 Net9
3/11/2005 10 Exp1 Exp2 Net10
3/18/2005 11 Exp1 Exp2 Net11
3/25/2005 12 Exp1 Exp2 Net12
4/1/2005 13 Exp1 Exp2 Net13

This only shows the first quarter and the first week of the 2nd quarter. Is
this similar to how you have your data set up? Then my second question would
be, what is it you want to carry ove to your quarterly report? All of the
detail of each quarter only or the subtotals of the quarter only?

Thanks,

 

[Guest]

You are going to have some problems. The Quarters do not break evenly between
weeks. Can you get a hold of the daily amounts. If so then we can set you up
with at pivot table which will automatically subtotal based on weeks, months
, quarters...What ever makes you heart go pitter patter... Otherwise you will
have to break up 4 of your weeks that straddle the quarters.

HTH

 

[Al vanderhoof]

If a week spans two quarters then it would be included in the first
quarter.
I basically want to carry everything over and then i would link it to a
P&L sheet.
Each quarter would have its own worksheet.

 

[Al vanderhoof]

If a week spans two quarters then it would be included in the first
quarter.
I basically want to carry everything over and then i would link it to a
P&L sheet.
Each quarter would have its own worksheet.

 

[Guest]

I think that we might still be bset off with a pivot table. Give this a try
and let me know what you think.

Select all of the data.
Click on Data -> Pivot Table
A wizard will open up. It is fairly self explanitory to go through the
steps. (You cna probably just push finish and it will make all of the correct
guesses. It will create a new sheet for you with a pivot table on it. There
will be a new menu with dates and all of your accounts on it. Drag the date
to the left column and the individual accounts to the middle. Select the
Dates and right click. Select group on and you can group by quarter.
Hopefully this is what you want. If this works for you in general and you wna
to know more just ask...

HTH

 

[Al vanderhoof]

It is a lot closer than anything I thought of so far.
Will the pivot table update as new information is added?

 

[Tom Ogilvy]

No. You have to refresh it and extend the source as well. You could do that
with a dynamic range for the source.

 

[Al V]

I cannot get the pivot table to accept more than one data field(column) for
the rest of the accounts.
The rest of it works pretty well so far.
I think I'm getting close.

 

[JulieD]

Hi Al

not sure exactly how you want the quarterly reports arranged but i'm
assuming that you've got the same columns on this report as you have on the
weekly report and what you want to see is:
.............A.......................B.......................C................................D..........................
1.........QTR...............Category1..............Category2..................Category3.........
2.........1st..................=SUMPRODUCT(--(WeeklySheet!$A$2:$A$54>DATEVALUE("1/1/2005")),--(WeeklySheet!$A$2:$A$54<DATEVALUE("31/3/2005")),WeeklySheet!B$2:B$54)

the formula in B2 can then be filled across to columns C, D, E etc
and down for other quaters, just change the values in the DateValue part of
the formula.

BTW WeeklySheet needs to be replaced with the actual sheet name of your
weekly numbers - if the sheet name contains a space you'll have to enclose
it in ' '

Cheers
JulieD

 

[Chris Rogers]

It seems like you could easlily base a pivot table off of this.
Assuming you use dates as the date field and not Week 1...Week 52, you
could then use grouping within the pivot table to group your report by
month, quarter or year.

 

[Guest]

Here are 2 pieces that might help:

1. Insert a New Column with Quarter (Col B)
Created from Week date: ="Q"&ROUNDUP(A2/13,0)
Created from Calender date: ="Q"&ROUND(MONTH(A2)/3,0)

2. Where col B is Q1, Sum every row in col C:
=SUMIF(B1:B52,"=Q1",C1:C52)

Ola Sandstrom


Note:
Quarter from weekdate will not entierly correct, as Jim points out.
Quarter from calendar date will be correct.