#### soundchaser99

##### New Member

- Joined
- Mar 30, 2006

- Messages
- 16

I am looking for a moving average that updates automatically as more data is entered, and uses a user defined figure to determine how far back to average.

The basics are as follows:

I have a list of weekly data (Week 1-52) contained in cells I7 - BH7.

These columns can contain either a number, a 0, or can be blank if I haven't reached that week yet.

AT the moment I am simply averaging all of the 52 columns, so if I have 26 weeks worth of data then I get an average of the 26 weeks.

What I want to do is to be able to place a number in a cell (doesn't matter where) which represents how far back (in weeks) the average works - so if I put 10 in the cell then the average for I7 - BH7 is calculated only for the last 10 columns, starting at the last column with a value in it.

So, if I have data in the first 20 columns, and the remaining 32 columns are blank, and I select 15 as my average, then the average will be calculated from column 20 back to column 5 (15 columns).

This would also need to update automatically as new data was entered, so that if I entered a value into the 22nd column, then the average would automatically adjust to move from columns 5-20 to columns 7-22.

I hope this makes sense

I also have another question which I might as well ask here too

Is there any way to get the sum of a number of cells, excluding those which are shaded grey?

I have a list of data in a column, some of which is not required for a particular report(hence the cell being filled in colour grey), and I need to total the sum of all the cells except those that are grey. Deleting those cells is not an option (I currently hide those rows when printing the sheet out so alternatively a formula that doesn't count hidden rows would also be good).