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

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.

• Thanks for letting me know, will edit Commented Nov 14, 2023 at 11:11

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().