Excel Subtotals

April 21, 2009 3:39 PM

So I have an array of data in Excel. Specifically, weekly repos outstanding (dollar amounts) since 2001. Is there any way to find monthly rate [(last date of month-first date of month)/first date of month] of change for it? What I have in mind is something like subtotals function except it shows rate of change rather than sum or average. Does anybody have any ideas?

This is what the data looks like. Note: this is public information, nothing super-secret, corporate espionage kind of thing.

Date |Total Repo
7/04/2001 |3,059,572
7/11/2001 |3,140,604
7/18/2001 |3,195,333
7/25/2001 |3,238,097
8/01/2001 |3,311,003
8/08/2001 |3,373,444
8/15/2001 |3,087,259
8/22/2001 |3,212,466
8/29/2001 |3,203,506
9/05/2001 |3,115,248

posted by Lucubrator to Computers & Internet (6 answers total)

Response by poster: Oh, and if this is easier in Minitab or SAS, I have access to those also. Thanks
posted by Lucubrator at 4:10 PM on April 21, 2009

Just to clarify the question a bit, do you want to see a rate of change next to each week's data? For example should 8/08/2001 show the percentage increase or decrease since 7/11/2001, 8/15/2001 vs. 7/18/2001, etc?

Or do you want just one calculation per month? Meaning you want it to figure out which is the last entry for a given month, which is the first, and calculate the percentage change between those?
posted by FishBike at 4:26 PM on April 21, 2009

Best answer: I assume the array starts in cell A1.

C1: =Year
D1: =Month
E1: =Day
F1: =MinDay
G1: =IsFirst

C2: =year(A2)
D2: =month(A2)
E2: =day(A2)
F2: {=MIN(IF($D$2:$D$10000=D10,$E$2:$E$10000))}
(you type it in without the brackets, and then when you're finished writing it hit Ctrl+Shift+Enter instead of just Enter.
G2: = if(F2=E2,1,0)

Copy the formulas down, then pivot table it:
- Year, then months on the left side (rows)
- Set IsFirst up top, and set to only show 1s
- Put your data in the data field
- Do the division to calculate month-to-month change.
posted by nyc_consultant at 4:33 PM on April 21, 2009

Best answer: Ok, assuming the latter interpretation from my clarification question (e.g. you want it to figure out the first and last entry for each month and calculate the % difference between them).

Setup: I pasted your example data into a blank worksheet starting at cell A3 (e.g. A3 contains the heading "date"). Then I inserted a blank line under the headings (so the first row with actual data in it is row 5).

In cell C5, enter the following formula:

=IF(MONTH(A5)<>MONTH(A4),B5,0)

Copy this all the way down column C until the end of your data. You should now have a column C that contains a copy of the dollar amount for dates which are the first date in that month, and 0 for all other entries in each month.

Now in cell D8 (which is beside the "end" of the first month), enter the following formula:

=IF(MONTH(A8)<>MONTH(A9),(B8-SUM(C1:C7))/SUM(C1:C7),"")

Copy this all the way down column D until the end of your data. You should now have a column D that calculates the % change from beginning of month to end of month, on those rows which are the last date in the month. For all other rows it shows blank.

The SUM(C1:C7) business is to pick up the beginning-of-month number from an uncertain number of rows above. It doesn't matter if there are 4, 5, or 6 entries for a month because all but the first one in the month will be a 0 anyway.

You can probably adapt this slightly if, as I suspect, it makes more sense to compare the end of one month to the end of the next month (and not beginning to end of month, which keeps leaving out a week's worth of changes!)
posted by FishBike at 5:06 PM on April 21, 2009

Response by poster: @Fishbike: That was exactly what I was looking for. Thank you!
@NYC_Consultant: I thought of myself as knowing the ins and outs of Excel but that was just inspired. Thank you.
posted by Lucubrator at 5:32 PM on April 21, 2009

The data that you are looking at looks as though its in weekly format and not monthly format. I do caution the use of difference on a month to month basis, as there is a possibility of there periodically being a 5-week month. (Week end 7/1/0X, Week end 7/8/0X, Week end 7/15/0X, Week End 7/22/0X, Week End 7/29/0X). You may find it more valid to compare median or average monthly values instead. Difference in an agregate (Mean/Average/Max/Min) also affords you some flexibility in normalizing out naturally occuring variation.

If you can go that route your audience needs to understand that the data is normalized and may not reflect actuals, but it does reflect what is directionally occuring. If you get buyoff as such, you could do the following:
ColumC {Row2-11}: =Date(Year(A2),Month(A2),1)
ColumnD {Row 2}: =Date(2001,1,1)
ColumnD {Row 3-11}: = Date(Year(D2),Month(D2)+1,1)
ColumnE {Row2-11}: =IFERROR(AVERAGEIFS($B$2:$B$11,$C$2:$C$11,$D2),0)

Now Column E will begin to fill in with the agregate average of all monthly values: So: 7/1/2001 winds up returing $3,158,401.5
8/1/2001 winds up returing $3,237,535.6
9/1/2001 winds up returing $3,115,248.0

With that information, not only does the difference in averages between 7/1/2001 and 8/1/2001 become relatively interesting, but also the difference in averages between 7/1/2001 and 7/1/2002. Personally, after I got the monthly average, I'd break out a Month and Year column and pivot the whole thing - you can get some good quick charts out of the pivot_chart...
posted by Nanukthedog at 6:15 PM on April 21, 2009