Problem: AVERAGEIFS of last x rows matching specific criteria

[azairvine]

Hi,

I am trying to create a formula that will return the average of specific columns from the last x rows *matching* specific criteria. For example:

https://i.imgur.com/MR2BSbL.jpg

The formula would need to return the average WR (column I) for the last x entries for Deck (column D) - "Meta Spell Hunter" and "Zoo Warlock" for instance.

Here on the statistics tab you can see that I already have the formula for filtering the results for specific Decks (column D), but I'm unable to figure out whether I can nest an INDEX or OFFSET command into this formula?

https://i.imgur.com/kczdv4T.jpg

The goal is to quickly access a trend of the last x games played with a specific deck (this is for Hearthstone if anyone has heard of it) so I can track my recent results - similar to L10 in professional sports.

Here's another one of the existing formulae:
<bdo dir="ltr">=IFERROR(COUNTIFS(Standard!$D$2:$D,B$1,Standard!$J$2:$J,"Win"))/(COUNTIFS(Standard!$D$2:$D,B$1))</bdo>

Somehow in there I want to add the condition to just count the last x entries.

Thank you in advance!

 

[Aladin Akyurek]

Care to post the sample in an Excel readable form along with the expected result?