0

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 all time prior to the current string in the column. (need assistance).

Struggling to set OR conditions in my AVERAGEIFS function to read from a particular string the previous 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

​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/1LFiYfiaZ9PBwlhEHSVEMibgQzBGDfFCQhx96erUZYvg/edit?usp=sharing

Expected end result:

enter image description here

1
  • Please insert another table to show the manually entered desired results you would like to get from the data you listed. Highlight clearly what makes this question different from your previous question. The formula you quote is probably not going to be of use here, so you could remove it to make the question a bit briefer. Nov 14, 2023 at 11:48

1 Answer 1

0

I think your formula might not be correct due to the usage of AVERAGEIFS(data!$D:$D,data!$B:$B,$A10,data!$A:$A,"<="&D$9). Since data!$A:$A is text, you can't use '<=' directly. Could you replace the entire formula with one that returns an array of results?

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.