0
=ArrayFormula(average(LARGE(($B$2:$B$2477=$B2)*(G$2:G$2477),ROW(INDIRECT("1:"&$D2)))))

Formula calculates the average value for the industry, but only includes the top 90% of the values. Formula works great, except when I try to copy it down the column it freezes or crashes google sheets. I need to do this on 10 more columns, so trying to figure out if there's a better way. Any ideas would be great. Really appreciate the community here and was able to put this together in the first place by reading through answers to other questions and combining them all.

If there's a better method to exclude the bottom x% of values in an averageif formula maybe that's a better route.

player0
  • 124,011
  • 12
  • 67
  • 124
user2513952
  • 43
  • 1
  • 1
  • 4
  • Is arrayformula() an excel function? – Solar Mike Feb 12 '21 at 17:24
  • arrayformula() is how you drop an array into a google sheet. https://stackoverflow.com/questions/66165655/google-sheets-arrayformula-complex-row-equation-repeats-first-result-in-every – user2513952 Feb 12 '21 at 17:36
  • I would suggest [Apps Script](https://developers.google.com/apps-script) - that way you can organize your execution far better and IMO more maintainable. Do you have a dummy sheet with data that I could demonstrate on? Ideally including desired output data. – iansedano Feb 15 '21 at 10:09

0 Answers0