[GetFrodo]

Any hints for how to get a 12-month moving average when the dates are irregular? i.e. I need to work out the row which contains the earliest date within the 12-month window.

[shhftw]

Originally Posted by GetFrodo

Any hints for how to get a 12-month moving average when the dates are irregular? i.e. I need to work out the row which contains the earliest date within the 12-month window.

Doesn't work!

[shhftw]

=AVERAGEIF([test:range],criteria,[calculated:range])

Can't seem to set the criteria to TODAY()-365 which seems the obvious logical test to me - it's probably syntax, but I'm no expert, HTH

[tealady]

Pretty unclear how you have this data structured, so more info will help.
You can look at the function DMIN which will identify the minimum item in a range

[GetFrodo]

I have a column of dates, which are in chronological order, but not regular. A few columns across is a column of numbers associated with the dates, and for each row I want to calculate the average of the numbers of the previous 12 months.

The right hand column is what I want to achieve.

e.g.:

13/10/2001     32
17/11/2001     43
12/04/2002     82     AV(32,43,82)
20/04/2002     13     AV(32,43,82,13)
20/10/2002     22     AV(43,82,13,22)

@shhftw: I don't think I have the averageif function (I'm using Office 2003), but I am also unsure if it can do what I want.

[Johnbee]

Have you tried putting the formula in the forst cell and then copying it to all the cells below it?
I don't use Excel but that would have worked in old spreadsheets.

[GetFrodo]

Johnbee, the problem is that there is no 'easy' formula that can be filled down. Due to the irregularity of the dates, the average needs to be taken over different sized ranges and hence the simple =AVERAGE(B1:B4) will not work.

[tealady]

A little prg would sort this, but in excel more tricky.
Use the DMAX function to work out the latest date., then take 365 days off this (ignoring leap Years). Compare each date to this to see if they are greater.
Using an if, make a formula such that if they are greater, put 1 else 0.
Use sumif to add up the values where the adjacent cell is 1, use countif to add up the 1s
Divide the sumif by the countif to get an average.

[whoever,hey]

It would be a lot easier with a VB macro.

[tealady]

Yes, but a rather steep learning curve.

[tealady]

You need to use the Max function or better the Large function rather than DMAX as suggested previously.
If you set up some named ranges for the data, it will be easier to add new dates without amending much.

[Johnbee]

OP I thinik you have the standard problem. This will sound a bit rude but you will just have to believe that I know about this stuff. I have read the thread and your posts extremely carefully, and I still don't know what you want.

So that is it. You can't do it because you have not clearly grasped what you want to do. Sit down with a bit of paper and write down exactly precisely what you want, leave no exceptions and difficult bits glossed over. Define the problem and the answer, don't yet worry about how excel is going to do it.

Once you know that, you will be able to do it. Just saying things like the dates are irregular, and junk like that, is failing to work it out. Wrap the damp towel round the brow and think.

The reason why explaining the thing to someone else often helps is because if we have to explain it to another person, we have to know what the problem is.

[JeffG1]

Originally Posted by Johnbee

OP I thinik you have the standard problem. This will sound a bit rude but you will just have to believe that I know about this stuff. I have read the thread and your posts extremely carefully, and I still don't know what you want.

Well it seemed pretty clear to me. For a particular entry, the OP wants to calculate the average of this and all previous entries that are dated not more than a year earlier.

[tealady]

Not sure what you are on about johnbee, the steps to solve it are outlined in #8, #11.

[whoever,hey]

I had no trouble understanding what the OP wanted either.

[spannerjago]

Originally Posted by GetFrodo

The right hand column is what I want to achieve.

e.g.:

13/10/2001     32
17/11/2001     43
12/04/2002     82     AV(32,43,82)
20/04/2002     13     AV(32,43,82,13)
20/10/2002     22     AV(43,82,13,22)

If I understand correctly the table above isn't quite correct and should look like this:

13/10/2001     32    AV(32)
17/11/2001     43     AV(32,43)
12/04/2002     82     AV(32,43,82)
20/04/2002     13     AV(32,43,82,13)
20/10/2002     22     AV(43,82,13,22)

If that's right then this array formula should do the trick.

=AVERAGE(IF($A$1:A1>=EDATE(A1,-12),IF($A$1:A1<=A1,$B$1:B1)))

You need to enter this using Cntl+Shft+Enter and then drag the formula down the length of the table. Obviously you may need to adjust the column references to suit your sheet.

Basically the EDATE is taking the date and going back 12 months. If a date in that range is >= the date - 12 months and <= to the date then the number will be included in the average.

Good luck.

[JeffG1]

Originally Posted by spannerjago

If that's right then this array formula should do the trick.

It's not, actually, because 13/10/2001 is more than a year before 17/11/2001. So the average on the 17/11 line should not include the 13/10 value.

[spannerjago]

Are you sure?

13/10/2001 is only a month and 4 days before 17/11/2001.

[JeffG1]

Whoops! Brain failure...