30, 60 or 90 day averages
 

How can I create a function to return a 30, 60, 90 date average based on a
date field. In other words, if date of a row is within the past month,
include it in the avg calculation. Thanks,

-BP-

30, 60 or 90 day averages
 

Bendleton wrote:
How can I create a function to return a 30, 60, 90 date average based on a
date field. In other words, if date of a row is within the past month,
include it in the avg calculation. Thanks,

-BP-

Hello,

If A1 holds your comparison date (i.e. TODAY()), column B your date
data and column C your data you like to average, then

=SUMPRODUCT(--(B1:B99-A1=0),--(B1:B99-A1<30),C1:C99)/SUMPRODUCT(--(B1:B99-A1=0),--(B1:B99-A1<30))

This is for 30 days rolling. If you need a special month, for example
December 2006 (enter any day of Dec 2006 into A1):
=SUMPRODUCT(--(YEAR(B1:B99)=YEAR(A1)),--(MONTH(B1:B99)=MONTH(A1)),C1:C99)/SUMPRODUCT(--(YEAR(B1:B99)=YEAR(A1)),--(MONTH(B1:B99)=MONTH(A1)))

For two or three months I would add two or three of these formulas.
Just take DATE(YEAR(A1),MONTH(A1)-1,1) instead of A1 for the month
before and DATE(YEAR(A1),MONTH(A1)-2,1) instead of A1 for two months
before A1.

Regards,
Volker

30, 60 or 90 day averages
 

Hi

One way would be with an array formula.
With dates in column A, values to be averaged in B, place the required
number of days (30 or 60 or 90) in C1
{=AVERAGE(IF(TODAY()-A1:A10<C1,B1:B10))}

Array formulae must be committed or Edited with Control+Shift+Enter
(CSE) not just Enter.
Using CSE, Excel will insert the curly braces { } around the
formula, do not type them yourself.
Change ranges to suit.

--
Regards

Roger Govier


"Bendleton" wrote in message
...
How can I create a function to return a 30, 60, 90 date average based
on a
date field. In other words, if date of a row is within the past
month,
include it in the avg calculation. Thanks,

-BP-

30, 60 or 90 day averages
 

Hi

=SUMIF(A:A,"=" & (TODAY()-30),B:B)/COUNTIF(A:A,"=" & (TODAY()-30))


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Bendleton" wrote in message
...
How can I create a function to return a 30, 60, 90 date average based on a
date field. In other words, if date of a row is within the past month,
include it in the avg calculation. Thanks,

-BP-