• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Rolling three month SUM

A

asajnani

New Member
Hi there,

I'm wondering if someone might have a work around/formula for what I’m trying to achieve.

I need to get the “rolling” three month aggregate figures for a dimension - let’s say Falls (in column E of the table below), for the ward at each campus.

The data looks as in the image.

As you can see, the year and month are separated out; so I’d need the formula to say something like “If datetoday = month of October 2013, then look at column B and retrieve the last three months (July, August, September 2013, for ward AAA in column D and aggregate the Falls figures from column E ”.

Thanks in advance for your help!
 
Hi, asajnani!
Nice image, very minimalist, so much that it's almost invisible. Without the "almost"... Please post the link.
Regards!
 
Hi Asajnani,

Please share the xls file ...
asajnani said:
Hi there,

I'm wondering if someone might have a work around/formula for what I’m trying to achieve.

I need to get the “rolling” three month aggregate figures for a dimension - let’s say Falls (in column E of the table below), for the ward at each campus.

The data looks as in the image.

As you can see, the year and month are separated out; so I’d need the formula to say something like “If datetoday = month of October 2013, then look at column B and retrieve the last three months (July, August, September 2013, for ward AAA in column D and aggregate the Falls figures from column E ”.

Thanks in advance for your help!
Click to expand...
 
Hi Hui,

Thanks for your response, managed to figure it out.

I'm looking for way simple way to get the following information into one cell:

I need to know the month names of the previous three months.

So, if today's month is November, display text "Data is for months August, September, October."

Hope this makes sense!

Thanks :)

Hui said:
Asajnani

I think Formula Forensics No. 35 which will be posted today, will do just that

If you can't wait the 6 hours until it is posted it is based on the answer I supplied here:
http://chandoo.org/wp/2009/04/28/calculate-moving-average/#comment-446514
Click to expand...
 
In case anyone is interested, this is the formula I used (a colleague helped me):

=IF(AND(Richmond!$D6=B$5,OR(DATEVALUE(Richmond!B6)=EOMONTH(TODAY(),-1)+1,DATEVALUE(Richmond!B6)=EOMONTH(TODAY(),-2)+1,DATEVALUE(Richmond!B6)=EOMONTH(TODAY(),-3)+1)),Richmond!E6,0)
 
You must log in or register to reply here.
Back
Top