Question: I have a table in Excel for which I would like to calculate average goals scored for `n` recent games with criteria being the team.

```
       Team         Score

         Real            4
         Real            4
         Barca           2
         Real            3
         Barca           2
         Real            3
         Barca           2

```

The problem is how to get the range dynamic in the `averageif` formula. A solution for the range could be `=$B$2:ROW (` where the row() input should be e.g. the 3rd occurrence of "Real"

How can I solve this without VBA?

Answer: 
You can use this formula:

```
=AVERAGEIF($A$3:INDEX($A:$A,AGGREGATE(15,6,ROW($A$3:INDEX($A:$A,MATCH("ZZZ",$A:$A)))/($A$3:INDEX($A:$A,MATCH("ZZZ",$A:$A))=$D$2),E2)),$D$2,$B$3:INDEX($B:$B,AGGREGATE(15,6,ROW($A$3:INDEX($A:$A,MATCH("ZZZ",$A:$A)))/($A$3:INDEX($A:$A,MATCH("ZZZ",$A:$A))=$D$2),E2)))

```

It will average the score based on the two inputs, team and occurrence.

[![enter image description here](https://i.stack.imgur.com/m5UKR.png)](https://i.stack.imgur.com/m5UKR.png)
Append a calculation sheet, from which you read the ranges. Make those cells protected after write so it's not immediately editable by the user.