Producing quarterly figures?

[martin_pentreath]

Hi,

I was here a few weeks back, and got some good advice for my financial
analysis spreadsheet.

I've been trying so solve a new puzzle this evening. I use a
SUMPRODUCT expression to give me annual expenditure figures for the
various categories I've used to breakdown my expenditure:

=-SUMPRODUCT((0+('Money data'!$B$1:$B$3439<DATE(YEAR(C$4), MONTH(C
$4)+1,1))),(0+(('Money data'!$B$1:$B$3439>=DATE(YEAR(C$4)-1,MONTH(C
$4)+1,1)))),(0+(('Money data'!$H$1:$H$3439=$B8))),(0+(('Money data'!$G
$1:$G$3439=$A8))),('Money data'!$F$1:$F$3439))

The worksheet "Money data" contains all the transactions. It has dates
of each transaction it records in column B. The category for the
transaction is in column H. The amount is in Column F.

The "Annual figures" worksheet (from which the above expression is
taken) calculates the total expenditure in each category for 12-month
periods. Column B contains a list of the categories; and row 4 has the
last month of the 12-month period to which the column below relates in
the form "01/03/08" (although in that example I would want figures for
the year *ending* March 08).

The first part of the expression (argument?) pulls out those
transactions from "Money data" which occured on or before the last day
of the month at the top of the column. The second argument eliminates
those transactions which occured more than a year before. So together
these pull out the figures for the year ending on the last day of the
month in question. Then the third argument pulls out only those
transactions within that date range which match the category for the
row.

This all seems to work fine. What I'd like to do now is to have
another worksheet containing quarterly figures. What I can't work out
is how to adjust the formula to pull out transactions for the three
months ending on the last day of the month at the top of the column.

Sorry for the longwinded post. I'm sure it can't be that hard. I'm
just having problems getting my head around it.

Cheers!

Martin

 

[Roger Govier]

Hi Martin

I think your existing formula can be expressed more simply as
=-SUMPRODUCT(
--('Money data'!$B$1:$B$3439<DATE(YEAR(C$4),MONTH(C$4)+1,1)),
--('Money data'!$B$1:$B$3439>=DATE(YEAR(C$4)-1,MONTH(C$4)+1,1)),
--('Money data'!$H$1:$H$3439=$B8),
--('Money data'!$G$1:$G$3439=$A8),
--('Money data'!$F$1:$F$3439)
)

For the figures for a quarter, try

=-SUMPRODUCT(
--('Money data'!$B$1:$B$3439<DATE(YEAR(C$4),MONTH(C$4)+1,1)),
--('Money data'!$B$1:$B$3439>=DATE(YEAR(C$4),MONTH(C$4)-2,1)),
--('Money data'!$H$1:$H$3439=$B8),
--('Money data'!$G$1:$G$3439=$A8),
--('Money data'!$F$1:$F$3439)
)

 

[ShaneDevenshire]

Hi,

I made a number of simplification to your formula as did Rodger and I set it
up for the last quarter. I am replaceing the calculation of the prior year
(or quarter) with the EOMONTH(C4,-12)+1 or EOMONTH(C4,-3)+1 functions. This
function is in the Analysis ToolPak so you must attach it by choosing Tools,
Add-ins and checking Analysis ToolPak.

EOMONTH(C4,N) returns the end of the month N months into the future or past
relative to C4. In this case if C4 = 3/30/2008 then 3 months back would
return 12/31/2007, to which you add 1 day to get the first.

My version of the formula is

=-SUMPRODUCT(--('Money data'!$B$1:$B$3439<(EOMONTH(C4,0)+1)),--('Money
data'!$B$1:$B$3439>=(EOMONTH(C4,-3)+1)),--('Money
data'!$H$1:$H$3439=$B8),--('Money data'!$G$1:$G$3439=$A8),'Money
data'!$F$1:$F$3439)

Further, if you use range names you really simplify formula like this
because you don't need the sheet reference, and since range names are
absolute by default there is no need for $ signs. So for simplicity lets
call the B1:B3439 range B, and the others H, G, and F. Your formula becomes:

=-SUMPRODUCT(--(B<(EOMONTH(C4,0)+1)),--(B>=(EOMONTH(C4,-3)+1)),--(H=$B8),--(G=$A8),F)

If these ideas help, please click Yes.