1

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:

enter image description here

enter image description here

enter image description here

​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

1
  • Thanks for letting me know, will edit
    – pfnanc
    Nov 14, 2023 at 11:11

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.