# Thread: Date Sensitive Average (200)

1. ## Date Sensitive Average (200)

I need a formula to calculate a rolling average. I am looking at sales by month and would like to create a formula to calculate the average based on the last 6 complete months. Example:

It is now May 4th, 2004. The formula would look at (Nov.

2. ## Re: Date Sensitive Average (200)

If we assume you have date values in A1:A500 and the corresponding sales in B1:B500 (change as desired), this ARRAY formula (confirm with ctrl-shift-enter), note it is all one line.

=AVERAGE(IF(ISNUMBER(\$B\$1:\$B\$500)*(\$A\$1:\$A\$500>=DA TE(YEAR(TODAY()),MONTH(TODAY())-6,1))*(\$A\$1:\$A\$500<=DATE(YEAR(TODAY()),MONTH(TODAY ()),0)),\$B\$1:\$B\$500))

Steve

3. ## Re: Date Sensitive Average (200)

If the dates and sales are in the cells Steve suggested, you could do something like this:

=AVERAGE(OFFSET(B1:B500,MATCH(MAX(A1:A500),A1:A500 )-1,0,-6,1))

4. ## Re: Date Sensitive Average (200)

My only concern would be if there were already (future) dates filled in A1:A500 that were just waiting for the sales figures. I will frequently fill a series with the values so save time later, so the max might not be the the one with the "current date"

Offset would also be a good way using match to find today, however. But ti depends on how the spreadsheet is setup. The way I listed is "generic" and the dates do not even have to be sorted to work.

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•