ARRAY TO AVERAGE 22 DAYS ON OR BEFORE DATE

[ja189704]

Hello.

I need to calculate the 22 day average of a stock at the end of a quarter.

So, in 2019 March 31 was on a weekend so the last day in March was 29. I want the average of the 22 days that ended on March 29.

If it ended on March 31 I am able to figure it out. But I suspect I need to use a match or something.

Thanks.

 

[offthelip]

Your row number don't quite make it into the picture but assuming the dates start in A1 and the date you are trying to match is in N11 then this equation should do it:

=AVERAGE(OFFSET($A$1,MATCH(N11,$A$1:$A$261,1)-22,4,22))

 

[ja189704]

Your row number don't quite make it into the picture but assuming the dates start in A1 and the date you are trying to match is in N11 then this equation should do it:

=AVERAGE(OFFSET($A$1,MATCH(N11,$A$1:$A$261,1)-22,4,22))

AMAZING! THANKS SO MUCH!