dynamic 12 month average?

[stuartwoodburn]

Hi,

Can anyone advise me how to achieve the following?

colum A - random dates
colum B - percentage audit scores

I need to keep a rolling average of the last twelve months of percentage audit scores. The difficulty being that audit scores are not entered on a regular or controllable basis. This makes the data difficult to use.

example

Col A Col B

01/01/2010 80%
01/01/2010 85%
04/01/2010 75%
01/02/2010 70%
04/05/2010 75%

how to i keep a rolling score of the last 12 months. Bearing in mind that at any point in time, the last 12 months will have varying numbers of contributing audits.

 

[northwolves]

Try formula in C1:

=TEXT(SUMIF(A$1:A100,">="&DATE(YEAR(A1-1),MONTH(A1),DAY(A1)),B$1:B100)/COUNTIF(A$1:A100,">="&DATE(YEAR(A1-1),MONTH(A1),DAY(A1))),"0%")

Regards
Northwolves

 

[SamTYler]

Average of SumIf column A > (Now - 365)

"I know Nutink" about writing SumIf statements. Sorry.

If you have an extra column, Fill Down this formula

=IF(A1>Today()-365,B1,0)

Then average that total

 

[stuartwoodburn]

SamTyler,

Thanks so much. thats a nice elegant solution. much appreciated. I can see a way forward with this project now, thanks.

Northwolves,

your formula worked, but the result always seemed to be a few percent out. I'm not an expert at excel so it may hav been the way i implemented it.

Thanks to you both.