Last n values of an AVERAGEIF formula

[johnog]

Hi Team,

I have the following, which works great

=AVERAGE(IF('Raw stats'!O:O=A8,'Raw stats'!AH:AH))

however, I'm wanting another formula for the average of the last n occurrences of this on the raw stats tab.

I've tried a few things and come up short. Any help would be great.

Cheers,
Johno

 

[StephenCrump]

Welcome to the Forum!

Try: =AVERAGE(TAKE(FILTER('Raw Stats'!AH:AH,'Raw Stats'!O:O=A8),-N))

I have assumed that "last" means those towards the bottom (e.g. rather than needing to check an attached date in some other column?).

If the formula finds fewer than N occurrences, it will average them. If you want a result only if there are at least N, we can adapt the formula.

 

[johnog]

Thanks very much for the welcome and the answer! Some quick sanity checks shows this is exactly what I was after.

Thanks again,
Johno

 

[StephenCrump]

That's good to hear, thanks for posting back.