Progressive average

[udrmichelle]

I'm working with a huge amount of data and need help setting up a progressive averaging formula. We have tracked data for each month from January 2006 to January 2015. Sometimes there isn't data for a certain month, but most months have data. I have set up the monthly average formula, but I can't figure out how to do the progressive one. What we need is an average starting at January 2006; we want to see when the average increases or decrease, by each month....think of how Yelp averages the stars... It goes back to the first review and divides it over x amount of months.
Is this even possible to do? It will cover 109 months and I want to be able to drag the formula down instead of editing it 109 times for the 200 departments we are tracking. I can provide an example, but I would have to create a new sheet with no identifying data in it. I will do that if someone says that this is possible to drag down. thank you.

[BadlySpelledBuoy]

Are you averaging by the each month progressively, i.e. each Jan, then each Feb etc. as years go by, or are you just averaging month by month over time regardless of which month?

BSB

[oeldere]

Looks a job for pivot table.

add a small excel file, without confidential information, and also add the desired result manualy in the sheet.

[udrmichelle]

It would start out with one month, then 2, 3 4 etc., all the way up to 109 months being included in the average. Does that make sense?

2006
Jan
jan+feb
jan+feb+march
jan+feb+march+april
etc., so that at the end, 109 months will be averaged into the last month. But we want to be able to see what the average was in May 2013 (which would include all data from months Jan 2006 to May 2013) or July 2007 (which would include all the data from Jan 2006 to July 2007).

[BadlySpelledBuoy]

Have a look at the attached. Is this something you can work with?

Note the $ in the AVERAGE formula. This locks the first row of data so as the formula is dragged down it averages the rows progressively.

BSB

[udrmichelle]

Ohhhhh thank you!!!! I think that will work perfectly!

I just have one other problem on my side with getting this to work. When I did the average for each singular month, some months have no numbers, so the error #DIV/0! is displayed. Do I have to fix the error (how?), or is there a way to skip over the errors but still but still do a progressive average like in the example you gave me?

[oeldere]

with iferror.

See the formula (yellow cell) in the file BSB made for you.

[BadlySpelledBuoy]

You could wrap the formula in IFERROR to hide the #DIV/0 - =IFERROR(YourFormulaHere,"")

OR

You could "forecast" the blanks and then include those in the average, but again this will very much depend on your data, what it's used for and how accurate it needs to be.

Or potentially other methods. It really does depend on your data. And with no sight of that or any real understanding of what you're trying to accomplish, it's difficult to suggest anything further.

BSB

[udrmichelle]

Thank you thank you thank you!!!!!! It works! Y'all did a great job considering I didn't provide a workbook example. Thank you!!!!

[BadlySpelledBuoy]

Happy to help. Thanks for the Rep point.

Don't forget to mark the thread as SOLVED if you're happy you have the solution.

BSB