quarterly reports

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

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,
"Al Vanderhoof" wrote:

> 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
>
>
>

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" wrote:

> 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
>
>
>

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

"Al Vanderhoof" <calldpi@tds.net> wrote in message
news:420beba5$1_1@newspeer2.tds.net...
>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
>

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.

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.