using Averageifs

[Analyst0000]

I am trying to find the average daily production for the previous 3 months on a lease. My problem is, how to define what months I want to average because months vary in number of days and sometimes that range may fall in the year before, i.e. November is within the previous 3 months of January, yet in another year

My column headings are as follows:
Date; Lease Name; Daily Production; Previous 3 Mo Daily Production Average

So the conditions are if the lease name is the same, and if the month falls within the previous 3 month range.

Thank you!

 

[xenou]

Hi, welcome to the board.

I'll bet there's other interesting solutions but here's one - I assume the basic problem is just a formula to evaluate the date.

Given dates in column A we can return a "beginning date" in Column B:
------------------------
|  1-Jan-11 | 1-Nov-10 |
| 10-Jan-11 | 1-Nov-10 |
| 31-Jan-11 | 1-Nov-10 |
|  9-Feb-11 | 1-Dec-10 |
| 18-Feb-11 | 1-Dec-10 |
| 27-Feb-11 | 1-Dec-10 |
| 28-Feb-11 | 1-Dec-10 |
|  1-Mar-11 | 1-Jan-11 |
| 10-Mar-11 | 1-Jan-11 |
| 19-Mar-11 | 1-Jan-11 |
| 31-Mar-11 | 1-Jan-11 |
|  1-Apr-11 | 1-Feb-11 |
| 10-Apr-11 | 1-Feb-11 |
| 19-Apr-11 | 1-Feb-11 |
|  1-May-11 | 1-Mar-11 |
------------------------

The formula in B1 is:
=DATE(YEAR(A1),MONTH(A1)-2,0)+1

You would use this in your AverageIfs with an expression such as
(Date1 >= (DATE(YEAR(Date2),MONTH(Date2)-2,0)+1))

Does that makes sense?
If not, provide a few (5 or 6) rows of sample data and what the desired outcome would be.

Xenou


P.S., I think it is your intention to set it up this way, but I'll note that has I have interpreted this Jan 1 and Jan 31 both incorporate the "3 month period" of Nov-Jan. Yet that's really much closer to a two-month span on Jan 1 and a real 3-month span on Jan 31.

 

[Analyst0000]

Thanks! That helps so much!