Excel functions · Tips and tricks

Rolling Rolling Rolling

How can one create an XLstatements reports showing rolling periods? Here’s an example of a twelve-month rolling report (click to expand):

rollpd

I’m using the good old “Solomon” demo data here, which has only a few months of data, so it looks a bit odd. The first thing I did was put a reference to my cell named Pd (A5) above my last column in cell N2. I also referenced my FiscYr cell below that in cell N3. Next I move to the column to the left and enter a formula in M2, referring to the value in N2:

rollpd2

This formula is:

=IF(N2=1,12,N2-1)

And I use another IF formula in N3, for the fiscal year:

=IF(M2<$N$2,FiscYr,FiscYr-1)

I then copy those two formulas to the left ten columns (by selecting the two cells M2 and N2 and then dragging the fill handle to the left).

To make the column headers, I used this formula in cell C6:

=LEFT(PDMONTH(CpnyID,C$2),3)&" "&C$3

PDMONTH is a XLstatements function that returns a month name from the period number, and I am using the Excel LEFT function to get just the first three letters in each month name. I then copy that formula to the eleven cells to its left.

For the PTDBAL functions of the report, I used the values in rows 2 and 3, making sure to make the row absolute and the column relative, e.g., cell C7 has this formula:

=PTDBAL(CpnyID,LedgerID,$A7,SubAcct,C$3,C$2)

I then copied that formula to all the rows and columns.

If you don’t want your report to include the rows with the period numbers and years (rows 2 and 3 in this example), you can hide the rows you inserted for them:

rollpd4

One thought on “Rolling Rolling Rolling

  1. Pingback: Does it blend?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s