Average Of Day Before Value

[pilot330]

Hi,

I need a formula for COL C, that will put the average of the previous days values from COL B.
For example the average for 07/07/2015 in COL A, was 46 in COL B. I need to put that value in COL C., and so on down through the spreadsheet.

Book2
	A	B	C
1	Date	prb^2	Average For Day Before
2	06/07/2015	25.00
3	07/07/2015	40.50
4	07/07/2015	0.00
5	07/07/2015	76.56
6	07/07/2015	66.94
7	08/07/2015	11.11	46.00
8	08/07/2015	69.44	46.00
9	08/07/2015	9.00	46.00
10	09/07/2015	44.44
11	10/07/2015	0.83
12	10/07/2015	11.11
13	10/07/2015	18.37
14	11/07/2015	58.48
15	11/07/2015	4.94
16	11/07/2015	56.25
17	11/07/2015	73.47
18	15/07/2015	14.06
19	15/07/2015	2.04
20	16/07/2015	11.11
21	16/07/2015	73.47
22	17/07/2015	0.00
23	17/07/2015	0.00
24	18/07/2015	25.00
25	18/07/2015	51.02
26	20/07/2015	64.00
27	20/07/2015	76.56
28	21/07/2015	21.30
29	21/07/2015	0.00
30	22/07/2015	85.21
31	22/07/2015	11.11
32	22/07/2015	25.00
33	22/07/2015	0.00
Sheet1

Thanks,
Paul

 

[jasonb75]

This works but based on your example you may want to round the results.

Book1
	A	B	C
1	Date	prb^2	Average For Day Before
2	06/07/2015	25	0
3	07/07/2015	40.49586777	25
4	07/07/2015	0	25
5	07/07/2015	76.5625	25
6	07/07/2015	66.94214876	25
7	08/07/2015	11.11111111	46.00012913
8	08/07/2015	69.44444444	46.00012913
9	08/07/2015	9	46.00012913
10	09/07/2015	44.44444444	29.85185185
11	10/07/2015	0.826446281	44.44444444
12	10/07/2015	11.11111111	44.44444444
13	10/07/2015	18.36734694	44.44444444
14	11/07/2015	58.47750865	10.10163478
15	11/07/2015	4.938271605	10.10163478
16	11/07/2015	56.25	10.10163478
17	11/07/2015	73.46938776	10.10163478
18	15/07/2015	14.0625	48.283792
19	15/07/2015	2.040816327	48.283792
20	16/07/2015	11.11111111	8.051658163
21	16/07/2015	73.46938776	8.051658163
22	17/07/2015	0	42.29024943
23	17/07/2015	0	42.29024943
24	18/07/2015	25	0
25	18/07/2015	51.02040816	0
26	20/07/2015	64	38.01020408
27	20/07/2015	76.5625	38.01020408
28	21/07/2015	21.30177515	70.28125
29	21/07/2015	0	70.28125
30	22/07/2015	85.20710059	10.65088757
31	22/07/2015	11.11111111	10.65088757
32	22/07/2015	25	10.65088757
33	22/07/2015	0	10.65088757
Sheet1

Cell Formulas
Range	Formula
C2:C33	C2	=IFERROR(AVERAGEIFS(B:B,A:A,MAXIFS(A:A,A:A,"<"&A2)),0)

 

[pilot330]

Thanks very much, works perfectly.
Would it be possible to modify the formula so that it could return the average X number of previous days. Lets say I wanted to get the previous 7 days, or 114 days....
Thanks again!

 

[Oeldere]

cell C3 =IF(A2<>A3,AVERAGEIF($A$2:$A$33,$A2,$B$2:$B$33),$C2) and drag down

 

[jasonb75]

Lets say I wanted to get the previous 7 days

If you mean previous 7 calendar days then try
=IFERROR(AVERAGEIFS(B:B,A:A,"<"&A2,A:A,">="&(A2-7)),"")

Noting that there are gaps in the dates, if you mean the last 7 days with entries then I think that it might need some creative thinking.

 

[pilot330]

Works fine, thanks very much.