moving average
 

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

moving average
 

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

"Hunter" wrote in message
...
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

moving average
 

=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)

"Hunter" wrote in message
...
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

moving average
 

Thanks much!!

"Bob Phillips" wrote:

=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)

"Hunter" wrote in message
...
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

moving average
 

Thanks much!!!

"Biff" wrote:

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

"Hunter" wrote in message
...
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