Rolling Average based on string order with multiple conditions in the same column in Google Sheets

Question

I'm trying to calculate the average $ amounts per category (X & Y) and their sub-categories (A,B,C,D,E) on a rolling basis based on past data that's on a string format. There are 2 types of calculations:

1. A rate applied to the future based on the previous results. (solved).
2. An average of the last 3 or 6 strings prior to the current one in the column. (need assistance).

Struggling to set OR conditions in my AVERAGEIFS function to read from a particular string the last 3 strings and their data on a rolling basis. I tried doing it with AVERAGE and FILTER but kept getting an error, so tried again with AVERAGEIFS but no positive results yet.

This is the current state of my formula:

​=IF(OR(D$9="",$A10=""),"",IFERROR( IF($B$3="Rate",C10*(1+$B$4), IF(AND($B$3="Average",$B$5="All Time",$B10=""),AVERAGEIFS(data!$D:$D,data!$B:$B,$A10,data!$A:$A,"<="&D$9), IF(AND($B$3="Average",$B$5="All Time",$B10<>""),AVERAGEIFS(data!$D:$D,data!$C:$C,$B10,data!$A:$A,"<="&D$9), IF(AND($B$3="Average",$B$5="Last 3",$B10=""), AVERAGEIFS(data!$D:$D,data!$B:$B,$A10, data!$A:$A,INDEX(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")),COUNTA(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")))-1), data!$A:$A,INDEX(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")),COUNTA(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")))-2), data!$A:$A,INDEX(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")),COUNTA(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")))-3), data!$A:$A,"<="&D$9), IF(AND($B$3="Average",$B$5="Last 3",$B10<>""), AVERAGEIFS(data!$D:$D,data!$C:$C,$B10, data!$A:$A,INDEX(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")),COUNTA(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")))-1), data!$A:$A,INDEX(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")),COUNTA(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")))-2), data!$A:$A,INDEX(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")),COUNTA(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")))-3), data!$A:$A,"<="&D$9), IF(AND($B$3="Average",$B$5="Last 6",$B10=""), AVERAGEIFS(data!$D:$D,data!$B:$B,$A10, data!$A:$A,INDEX(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")),COUNTA(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")))-1), data!$A:$A,INDEX(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")),COUNTA(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")))-2), data!$A:$A,INDEX(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")),COUNTA(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")))-3), data!$A:$A,INDEX(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")),COUNTA(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")))-4), data!$A:$A,INDEX(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")),COUNTA(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")))-5), data!$A:$A,INDEX(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")),COUNTA(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")))-6), data!$A:$A,"<="&D$9), IF(AND($B$3="Average",$B$5="Last 6",$B10<>""), AVERAGEIFS(data!$D:$D,data!$C:$C,$B10, data!$A:$A,INDEX(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")),COUNTA(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")))-1), data!$A:$A,INDEX(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")),COUNTA(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")))-2), data!$A:$A,INDEX(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")),COUNTA(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")))-3), data!$A:$A,INDEX(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")),COUNTA(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")))-4), data!$A:$A,INDEX(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")),COUNTA(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")))-5), data!$A:$A,INDEX(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")),COUNTA(UNIQUE(FILTER(data!$A:$A,data!$A:$A<>"")))-6), data!$A:$A,"<="&D$9)))))))),"ERROR"))

Tables:

Col1	Category 1	Category 2	Col4	Col5
Apple-1	X	A	$0.00	$1,000.00
Apple-1	Y	C	$15.00	$0.00

Screenshots of Data and Views:

​It would be of great help if somebody could help me solve this complex problem. My ideas are to potentially add a sequential list of numbers assigned to each string, so that the AVERAGEIFS formula can figure out at which strings in the past to look for.

Please find below Edit access to the public GSheet:

https://docs.google.com/spreadsheets/d/128VtSYg8Cy6ldlxxVNsi5NNgrGVoAVgCukN0PvQBVeA/edit?usp=sharing

Answer 1

To get row-by-row totals, use this pattern:

=vstack( 
  "Total", 
  map(D2:D, E2:E, lambda(d, e, 
    iferror(1 / (d + e) ^ -1) 
  )) 
)

To get the rolling average of the current row and the two or five rows above it, put 3 or 6 in cell H1 and use this pattern:

=vstack( 
  "Rolling average of", 
  map(F2:F, lambda(f, 
    if( 
      isblank(f) + (row(f) < H1 + 1), 
      iferror(ø), 
      iferror(average(offset(f, 1 - H1, 0, H1))) 
    ) 
  )) 
)

To group the rolling averages by category 1 and category 2, and pivot them by fruit, use query(), like this:

=query( 
  A1:G, 
  "select Col2, Col3, sum(Col7) 
   where Col1 is not null 
   group by Col2, Col3 
   pivot Col1", 
  1 
)

See rolling average.

Also see map(), lambda(), vstack(), filter(), iferror() and query().