AverageIF and Offset Issue

**Parallax** · 10-14-2009, 06:13 PM

I'm trying to make a formula that will calculate the average of the previous 12months. The goal is to tie the formula to a reference cell that contains a date. Each time the date is changed by a user the calculation will be updated accordingly. Here is the CSE formula that I thought would work:

{=AVERAGE(IF($B$5:$B$53=B$2,OFFSET($C$5,(COUNT(C5:$C$53)-1),0,12,1)))}

I've also attached a sample file to illustrate the problem. The 'range' portion ($C$5,(COUNT(C5:$C$53)-1)[/B]of the Offset function was setup simply to get the 12 months which preceeded the reference date. I'm reasonable sure this isn't the problem. Any help is appreciated.

**daddylonglegs** · 10-14-2009, 06:46 PM

You don't really need the If part. If you want to average the last 12 values in column C try

=AVERAGE(OFFSET($C$5,COUNT(C5:$C$53)-12,0,12))

although you might have to modify that if there were less than 12 values....

**Parallax** · 10-15-2009, 09:30 AM

Thanks daddylonglegs,

You're absolutely right however I was trying to make this a conditional formula (it wouldn't always be the last 12 rows). I'd like to make cell B2 a date reference that can be changed by the user. The formula will look at B2, find the corresponding date in range B5-B53 and perform an offset that will grab the previous 12 months worth of data from column C and return an average. Is there a better way to go about this instead of the AverageIF and Offset approach?

**DonkeyOte** · 10-15-2009, 09:38 AM

Given use of 2007 I would suggest using the AVERAGEIFS function combined with EDATE, eg:

=AVERAGEIFS(C$5:C$53,$B$5:$B$53,">="&EDATE($B$2,-11),$B$5:$B$53,"<="&$B$2)
copied across as required