moving average

H

Hunter

If I have a column of dates (column A) which are in order but not sequential
and a corresponding column of values(column B), how can I calculate an
average for the values dating back one year from the last cell.

Column A Column B Column C
6/1/97 5 5/1=5
10/3/97 10 15/2 = 7.5
5/7/98 3 13/2 = 6.5
10/4/98 78 91/3= 30.3
etc
 
B

Biff

Hi!

What date format are you using?

Using the format, m/d/y I get different results: 5 - 7.5 - 6 - 40.5

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=AVERAGE(IF((A$2:A$5<=A2)*(A$2:A$5>=DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))),B$2:B$5))

Biff
 
B

Bob Phillips

=AVERAGE(IF((A1:A100>=MAX(A1:A100)-365)*(A1:A100<=MAX(A1:A100)),B1:B100))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
H

Hunter

Thanks much!!

Bob Phillips said:
=AVERAGE(IF((A1:A100>=MAX(A1:A100)-365)*(A1:A100<=MAX(A1:A100)),B1:B100))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
Click to expand...
 
H

Hunter

Thanks much!!!

Biff said:
Hi!

What date format are you using?

Using the format, m/d/y I get different results: 5 - 7.5 - 6 - 40.5

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=AVERAGE(IF((A$2:A$5<=A2)*(A$2:A$5>=DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))),B$2:B$5))

Biff
Click to expand...
 
Top