Weekly Totals into Monthly Totals

[briangriffin]

I have weekly figures autopopulating Excel:

9/13/09 18619
9/20/09 21885
9/27/09 19511
10/4/09 19647
10/11/09 17453
10/18/09 18827
10/25/09 21117
11/1/09 14723
11/8/09 21684
11/15/09 19067
11/22/09 18798
11/29/09 19434


I need to roll these into monthly figures instead:

9/2009 60015
10/2009 77044
11/2009 93706


I'm betting that there is a creative way to do this without VBA - thanks in advance for your help.

 

[gizmo1973]

You could format the date as MMM and then use the subtotals system based on the change of month to give you a total for each month (based on change of month column)

That's how I've done similar in the past

or if the date is important to be preserved introduce a new column with either the =month() function or just a copy of the date formatted as MMM etc

----------------------------------------
This ^ Line indicates the end of my post, apart from the bit below which is my signature

Regards, Phil.

http://www.mmse.co.uk

 

[SkipVought]

Hi,

Use the PivotTable wizard. But first you need valid table with headings: no empty rows or columns.

When you have the Date field in the ROW area and the amount field in the DATA area, right click the date field heading in the PT, and select Group & show detail > Group

In the Grouping Window select Month and Year

Took me less than 15 seconds to get...
[tt]
Sum of Amt
Years Dte Total
2009 Sep 60015
Oct 77044
Nov 93706
Grand Total 230765
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

Use a Pivot Table, Group by date. Done.

But bear in mind that some months will contain 5 datapoints while others only contain 4. This can cause your data to artificially indicate spikes that aren't really there.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

With respect to anotherhiggins' comment regarding data spikes, it is nearly always preferable to analyze and report raw data rather than reporting from an aggregation, which in your case is WEEK totals, which can skew results.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[briangriffin]

All good information, thanks. What I didn't mention is that I'm not using full Excel (hence the no VBA restriction) -I'm building a dashboard with Xcelsius, which includes most Excel funtionality but not all.

I'm not surprised that I don't have the ability to create pivot tables, but I'm a little surprised that I don't have subtotaling.

So, with that being said, does anyone have a purely formula based solution?

Thanks again.

 

[SkipVought]

I'm building a dashboard with Xcelsius

I have weekly figures autopopulating Excel

This forum is MS Office

You need to be in a CR forum. As you see, you get advice specific to Excel.

So will the real question please stand up.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[briangriffin]

Sorry, my attempt to keep the question simple got in the way this time.

Now that the parameters are clear, the question remains - converting data using standard Excel functions. I still think I'll get better information here.

Lesson learned, thanks.

 

[SkipVought]

So do you have a solution?

We do like to see a resolution posted.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[briangriffin]

No solution yet... I'm thinking a couple of passes with some kind of case statements might work. The worksheet can be ugly since it won't be seen. Thanks.

 

[xlhelp]

It's only out of curiocity, that I looked into this. How about

=IF(MONTH(A2)=MONTH(A1),"",SUM(IF(MONTH($A$2:$A$13)=MONTH(A2),$B$2:$B$13,0))) as an array formula ?

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[SkipVought]

Here's a non-PivotTable solution.

Use MS Query to generate monthly totals, assuming your data is in Sheet1, with headings Dte, Amt...

Select
  DateSerial(Year(Dte),Month(Dte),1), AS MO_DTE
, SUM(AMT) AS TOT_AMT
From [Sheet1$]
Group By
DateSerial(Year(Dte),Month(Dte),1)

You could also use the SUMPRODUCT function, to sum by month, generating your own MO_Dte column as first of month.
[tt]
=SUMPRODUCT((Dte>=D2)*(Dte<D3)*(Amt))
[/tt]
where column D has the first of month dates.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[blister911]

I'm assuming that the date and weekly figures are in two different columns. If so, this might work for you:

I only did what you listed but you can modify it for an entire year.

	A	        B	   C		D		E
1			         09/2009   10/2009   11/2009	
2			         60015	 77044	 93706	 
3   9/13/2009  18619  18619			
4   9/20/2009  21885  21885			
5   9/27/2009  19511  19511			
6   10/4/2009  19647			19647		
7  10/11/2009  17453			17453		
8  10/18/2009  18827			18827		
9  10/25/2009  21117			21117		
10  11/1/2009  14723					  14723
11  11/8/2009  21684					  21684	
12 11/15/2009  19067					  19067	
13 11/22/2009  18798					  18798	
14 11/29/2009  19434					  19434

C2 formula (Sept total): =SUM(C3:C14)
C3 formula: =IF(MONTH(A3)=9,B3,"")

D2 formula (Oct total): =SUM(D314)
D3 formula: =IF(MONTH(A3)=10,B3,"")

E2 formula (Nov total): =SUM(E3:E14)
E3 formula: =IF(MONTH(A3)=11,B3,"")

Then copy formulas in C3:E3 to C4:E14



Light travels faster than sound. That's why some people appear bright until you hear them speak.

 

[SkipVought]

ONE FORMULA for the entire thing!
[tt]
=IF(MONTH($A3)=month(C$1),$B3,0)
[/tt]
I would not use "": rather use 0. You can "hide" the ZEROS using Conditional Formatting.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[blister911]

Skip,
Good catch. I added the totals and headings to the top after I had done the rest of the sheet. I didn't think about updating the formulas.

d


Light travels faster than sound. That's why some people appear bright until you hear them speak.

 

[briangriffin]

Thanks to everyone for their input - the last solution works perfectly.

There is a potential problem in that the figures are a rolling six month period. The sorting would be an issue if you hardcoded dates across the top, and if the six months crossed years.

Since I'm populating the sheet from a SQL query, I can bring the dates in already sorted. But I'm wondering how that might be handled from within Excel - some kind of lookup using minimums, I would think. Doesn't matter, I'm good to go, thanks again.

 

[SkipVought]

[tt]
=IF(AND($A3>=C$1,$A3<D$1),$B3,0)
[/tt]


Do NOT hard code dates. Use a formula to caclulate the next first-of-the-month date. Then either hard code the FIRST date or make that data a suitable calculation.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}