google-sheetsgoogle-sheets-formula

Calculate average of last 5 last rows with Sheets formula


I'm looking for a formula to calculate an average of the results of the last 5 lines. It must meet the following conditions:

Here's my Sheets file with the expected results in column J. I'm not at all comfortable with formulas in Sheets.


Solution

  • Here's one approach you may test out:

    =byrow(E2:G;lambda(Σ;if(counta(Σ)=0;;let(Λ;bycol(chooserows(E2:index(G:G;row(Σ));sequence(min(row(Σ)-1;5);1;-1;-1));lambda(x;sum(x)));
     average.weighted(E1:G1;switch(Λ;;0;Λ))))))
    

    enter image description here