Array formula with nested functions issue

[vruiz1]

Please see the following file: https://dl.dropbox.com/u/32020573/121023-Exchange%20rate.xlsx


I have been trying in vain to write a formula that finds the average exchange rate value for the period spanning a full year before a specific date. When the specific date in question is the last day of the year, it is simple as it is the average for all values in that calendar year (as in cells C10 and E10). However, I cannot find a way to do this for end of August values, which are the cells shaded in green in the attached document. I have checked the array formula several times and cannot find any mistake with it, however I keep getting zero for an answer.


Any help would be much appreciated

 

[Haseeb A]

Can't use AND in arrays, use two IF formulas;


=AVERAGE(IF($B$16:$B$989>DATE(YEAR(D9)-1,MONTH(D9),DAY(D9)),IF($B$16:$B$989<=D9,$C$16:$C$989)))


Based on your file, assuming you are on XL2007 or later, If so you can simplify to AVERAGEIFS,


=AVERAGEIFS($C16:$C989,$B16:$B989,">"&EDATE(D9,-12),$B16:$B989,"<="&D9)


Copy to other block.

 

[vruiz1]

Thanks! That works perfectly, I was unaware that the AND function wouldn't work with arrays, is that the case for other functions as well?


Regards,


V