• 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 Moving average

B

BWCY

New Member
Hi,


I'm trying to get an average of the last 3 periods. My data is horizontal (ie - F3, G3, H3, I3). I've tried using the offset formula but doesn't quite seem to work. Also, is there a way of ignoring a cell if it is blank? So if G3 is blank, it calculates the average of F3, H3, I3?


Thanks for your help!
 
Hi BWCY,


Firstly welcome to the Chandoo's forum!! You can use following formula instead:


=SUM(F3:H3)/COUNTA(F3:H3)


....to be entered in H4 and will find the average of last three months. Logic behind this is that if a cell in the range F3:H3 is blank, COUNTA() will result 02 (COUNTA() is used to find No. of Non-Empty Cells). Hope it will work.


Regards,

Faseeh
 
Thanks Faseeh. Definitely learning a lot from the forum!


I don't think I was clear enough with my question. I'm looking at a rolling MA. So say I have the last 3 values as 2,4,6...then the average would be 4. If the next value is 8 then the 3 period MA would change to 6. Not sure if I can still use COUNTA.


Also say I am missing a month so my values are 2,4,(no value),8, is there a formula that would ignore the missing month and give me the average of the 3 months which did have a value ie 2,4,8? I've got a lot of data, so looking for a formula that I can copy down as the new month's data is added.


Thanks!
 
Hi BWCY,


When we talk about 'Rolling or Moving Average', that means that we take a period, lets say of 04 months, we add a new month, and delete the last one. For example we have data set of Jan, Feb, Mar, Apr then Moving Average will be


- the average of four months for 'April'.

- the average of Feb to May for 'May'

- the average of Mar to Jun for 'Jun'


...and so on.


If you want to calculate 'Cumulative Average' i.e from the first month to latest month average that is another thing.


The formula i mentioned will account for any missing values. Lets say if in a range of three cells only two contain nos and one is blank then this formula will give you average based on only two cells. If still unclear let me know about it.


Regards,

Faseeh
 
HI,


Use a cell where you enter the month number and use that with the offset to give you the sum of the 3 months as follows:


=SUM(OFFSET(B2,0,$A$1,3)/3) in Cell B1

This formula assumes that you have put the month number A1, your monthly data is in range B2 onwards and the result is in B1
 
You must log in or register to reply here.
Back
Top