Moving average in a column

[Kirit George]

Hi,


I wanted to calculate the moving average in data which is updated weekly. Wanted to get the latest 3 weeks' average.


The data is as follows:

[pre]

Latest 3 Averages
Name	Average Accuracy			WE May 1			WE May 7			WE May 14			WE May 21			WE May 28
CE	NCE	Minor
User 1	98.86666667			97			100			99.6			99.4			99.5
2.666666667	15	27	0	15	35	4	20	25	4	15	30	1	20	25	0	15	30
User 2	99.5			99.5			99.3			99.7			99.3			99.7
3.333333333	14	37	6	15	30	4	20	25	0	15	30	1	20	25	0	15	30

[/pre]
Requesting a solution from you to calculate the moving average. The criteria is as follows:


1. Want to calculate the moving average from the data that is entered in the columns on the right side on a weekly basis. The data is to be entered under the column Latest 3 Averages, in the column B4 and B6 and so on which is fixed and should get updated.

2. The data below average accuracy has 3 different cells, have to calculate the moving average for all the three moving values. B5 should have the average from E,H,K,N,Q and so on. C5 should have the average from F,I,L,O,R. D5 should have an average from G,J,M,P,S.

3. Should have the specification to exclude the spaces or blanks and should not be included in the calculation.

 

[Hui]

Krit


Firstly, Welcome to the Chandoo.org forums


Can you please post a sample file, as its hard to work out columns based on the data above

refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[Kirit George]

Hi,


I wanted to calculate the moving average in data which is updated weekly. Wanted to get the latest 3 weeks' average.


The sample is in the following link https://www.box.com/s/nq39ve1b001797hcgc6s


Requesting a solution from you to calculate the moving average. The criteria is as follows:


1. Want to calculate the moving average from the data that is entered in the columns on the right side on a weekly basis. The data is to be entered under the column Latest 3 Averages, in the column B4 and B6 and so on which is fixed and should get updated.

2. The data below average accuracy has 3 different cells, have to calculate the moving average for all the three moving values. B5 should have the average from E,H,K,N,Q and so on. C5 should have the average from F,I,L,O,R. D5 should have an average from G,J,M,P,S.

3. Should have the specification to exclude the spaces or blanks and should not be included in the calculation.

 

[SirJB7]

Hi, KiritGeorge!


Check if this works:


a) I assume that you'll be adding groups of three columns for each week at the rightmost of the last used column, so no blank or empty columns.


b) I assume that you'll have at least 3 weeks in your worksheet.


c) I assume that 2nd row of 1st user (row 5) will have all the three values for each week, even if zero, but not blank (otherwise you should change the design of row 2).


d) Define a dynamic named range as:

DataTable: =DESREF(Sheet1!$E$4;;;(CONTARA(Sheet1!$A:$A)-1)*2;CONTARA(Sheet1!$5:$5)-3) -----> in english: =OFFSET(Sheet1!$E$4,,,(COUNTA(Sheet1!$A:$A)-1)*2,COUNTA(Sheet1!$5:$5)-3)


e) Place this formula in the 1st average (cell B5) and copy across and down as required:

=PROMEDIO(INDIRECTO(DIRECCION(FILA();COLUMNA(DataTable)+COLUMNAS(DataTable)-5+COLUMNA()-0;4;1));INDIRECTO(DIRECCION(FILA();COLUMNA(DataTable)+COLUMNAS(DataTable)-5+COLUMNA()-3;4;1))*INDIRECTO(DIRECCION(FILA();COLUMNA(DataTable)+COLUMNAS(DataTable)-5+COLUMNA()-6;4;1))) -----> in english: =AVERAGE(INDIRECT(ADDRESS(ROW(),COLUMN(DataTable)+COLUMNS(DataTable)-5+COLUMN()-0,4,1)),INDIRECT(ADDRESS(ROW(),COLUMN(DataTable)+COLUMNS(DataTable)-5+COLUMN()-3,4,1))*INDIRECT(ADDRESS(ROW(),COLUMN(DataTable)+COLUMNS(DataTable)-5+COLUMN()-6,4,1)))


Just advise if any issue.


Regards!


EDITED:


PS: 2 things:

1) Get rid of a circular reference which I didn't see previously.

2) Get rid of row 5 cell conditions.


Formula remains equal but named range definition changes to:

DataTable: =DESREF(Sheet1!$E$4;;;(CONTARA(Sheet1!$A:$A)-1)*2;COINCIDIR("X";Sheet1!$2:$2;0)-5) -----> in english: =OFFSET(Sheet1!$E$4,,,(COUNTA(Sheet1!$A:$A)-1)*2,MATCH("X",Sheet1!$2:$2,0)-5)


Place an "X" in the next cell of last used cell in row 2.