Rolling average, variable number of cells

[bedalhome]

I want to report rolling average in ColD, but I want to change the number of points used. Also, I do not always have temperature readings so I want the number of points used to be the last N temps taken.


Date,Temp,RollAveCount,Roll Average
5/29/09 185.2 1 185.2
6/1/09 185.0 2 185.1
6/3/09 185.0 3 185.1
6/6/09 185.2 3 185.1
6/9/09 3 185.1
6/12/09 185.0 3 185.1
6/15/09 185.8 4 185.3
6/18/09 4 185.3
6/20/09 185.6 4 185.4
6/25/09 4 185.4
6/29/09 184.4 3 185.3

 

[sanrv1f]

not very clear

what is the N here? is it the number in column C?

could you post some sample data with expected results as a table with borders ? ( if you are using IE you can simply copy paste a range with all borders from excel to the board, otherwise try with excel jeanie (see my signature))

 

[bedalhome]

I want to report rolling average in ColD, but I want to change the number of points used. Also, I do not always have temperature readings so I want the number of points used to be the last N temps taken.

Excel Workbook
	A	B	C	D
3	Date	Temp	RollAveCount	Roll Average
4	5/29/09	185.2	1	185.2
5	6/1/09	185.0	2	185.1
6	6/3/09	185.0	3	185.1
7	6/6/09	185.2	3	185.1
8	6/9/09	*	3	185.1
9	6/12/09	185.0	3	185.1
10	6/15/09	185.8	4	185.3
11	6/18/09	*	4	185.3
12	6/20/09	185.6	4	185.4
13	6/25/09	*	4	185.4
14	6/29/09	184.4	3	185.3
Sheet1

 

[sanrv1f]

try this

Excel Workbook
	A	B	C	D
1	Date	Temp	RollAveCount	RollAverage
2	29/05/2009	185.2	1	185.2
3	01/06/2009	185	2	185.1
4	03/06/2009	185	3	185.06667
5	06/06/2009	185.2	3	185.06667
6	09/06/2009		3	185.06667
7	12/06/2009	185	3	185.06667
8	15/06/2009	185.8	4	185.25
9	18/06/2009		4	185.25
10	20/06/2009	185.6	4	185.4
11	25/06/2009		4	185.4
12	29/06/2009	184.4	3	185.26667
Sheet8

#VALUE!

 

[bedalhome]

You are amazing sanRV1F. Thank you so much!