Average Offset Index Match formula

Question

I'm trying to average the most recent 6 values of 'x' - however, I'm getting reference errors when trying to average them. The value I'm looking for should be 8,720.33 with the criteria of 'x'. The dates have no bearing in the formula, just a visual reference point.

Formula I'm getting lost on "=AVERAGE(OFFSET(INDEX(C2:C42,MATCH(E2,B2:B42,0),),0,0,-6))"

*Note - the dates are being updated daily to include historical data points

Data Set:

Answer 1

You can try following array formula:

=AVERAGE(INDEX(C1:C25,N(IF(1,AGGREGATE(14,6,ROW(A1:A25)/(B1:B25=E2),ROW(A1:A6)),0))))

or with full ranges:

=AVERAGE(INDEX(C:C,N(IF(1,AGGREGATE(14,6,ROW(A:A)/(B:B=E2),ROW(A1:A6)),0))))

Confirm it with ctrl + shift + enter

Answer 2

You might be able to pull this off with AVERAGEIFS if you add a rank column that ranks Header1 by date.

In my example:

Column D is the Values you are averaging
Column B is Hearder1
Column C is the rank - =COUNTIFS($B:$B,B2,$A:$A,">"&A2)+1

=AVERAGEIFS($D:$D, $B:$B, "x", $C:$C, "<7")