30, 60 or 90 day averages

[Guest]

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-

 

[Volker]

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

 

[Roger Govier]

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.

 

[Arvi Laanemets]

Hi

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