help with a running average formula

**jacflash** · 06-24-2010, 06:15 AM

Hi i was wondering if anyone can help. I've got a spreadsheet which records attendance figures for each week. I've got all the totals and a weekly average but i want to include a running weeklt total average. Any help with this would be greatly appreciated as the way i've done it is very long winded and excel comes up with an error at week 33 as there is to many references for the formula to work.

**teylyn** · 06-24-2010, 06:29 AM

Sorry, but this is a classic case of bad spreadsheet design.

Organise your data in one table, then do the analysis on a separate tab or in a separate area of the sheet, instead of interspersing it with the data. That way, you can use one formula per metric, and just copy it down, instead of amending the formula for every occurrence.

**zbor** · 06-24-2010, 06:30 AM

And get rid of merged cells

**NickyC** · 06-24-2010, 06:34 AM

Hi
maybe not the most elegant solution, but why don't you just move your weekly totals and averages to another column (e.g. H, I or J). Then you could use the simple formula =average(D$6:D???)