google sheets - Filtering out the first X non-empty cells in each row of a matrix

Question

I'm trying to add some QOL to my spreadsheet's maintenance by replacing a long list of vector formulas with just a few matrix formulas. Computing the average via MMULT is fairly easy. However I have a list of tournament results that goes back over 3 years and therefore another thing I compute is every player's average over the last 8 tournaments they participated in.

While I eventually managed to find the first 8 non-empty entries of each row individually, I have no idea how I'd do it for all of them at once in a single formula. If someone happens to have an idea, I'd appreciate it. Or if someone can tell me not to waste my time on this, that's fine too.

Here's the spreadsheet in question, but there is no attempt on there to get this done yet: https://docs.google.com/spreadsheets/d/1beVUaOaZtWyFAQ-NsgK1SJjo02av2IANU87vS-yOKM0/edit?usp=sharing

Edit: Thank you to player0, the answer did exactly what I was hoping for. I'll try to eventually actually understand what is going on in that formula.

question from:https://stackoverflow.com/questions/66066379/filtering-out-the-first-x-non-empty-cells-in-each-row-of-a-matrix

Answer 1

use in C2:

=ARRAYFORMULA(IFNA(VLOOKUP(A2:A; QUERY({Results!A5:A127 
 ARRAY_CONSTRAIN(SPLIT(TRANSPOSE(QUERY(SORT(TRANSPOSE(
 IF(NOT(ISBLANK(Results!B5:DN127)); 
 (Results!B5:DN127-1)/(Results!B4:DN4-1); )); 
 TRANSPOSE(Results!B3:DN3); 0);;9^9)); " ");9^9; 8)}; 
 "select Col1,(Col2+Col3+Col4+Col5+Col6+Col7+Col8+Col9)/8*4+1 
  where Col9 is not null"); 2; 0)))