I'm looking for a formula to calculate an average of the results of the last 5 lines. It must meet the following conditions:
If there is only one line (line 2 as line 1 contains the labels), the result must be equal to
(E2xE1+F2xF1+G2xG1)/(E2+F2+G2)
If there are two lines, the result must be equal to
((E2+E3)xE1+(F2+F3)xF1+(G2+G3)xG1)/(E2+E3+F2+F3+G2+G3)
If there are three lines, the result must be equal to
((E2+E3+E4)xE1+(F2+F3+F4)xF1+(G2+G3+G4)xG1)/(E2+E3+E4+F2+F3+F4+G2+G3+G4)
If there are four lines, the result must be equal to
((E2+E3+E4+E5)xE1+(F2+F3+F4+F5)xF1+(G2+G3+G4+G5)xG1)/(E2+E3+E4+E5+F2+F3+F4+F5+G2+G3+G4+G5)
If there are five lines, the result must be equal to
((E2+E3+E4+E5+E6)xE1+(F2+F3+F4+F5+F6)xF1+(G2+G3+G4+G5+G6)xG1)/(E2+E3+E4+E5+E6+F2+F3+F4+F5+F6+G2+G3+G4+G5+G6)
If there are six lines, the result must be equal to
((E3+E4+E5+E6+E7)xE1+(F3+F4+F5+F6+F7)xF1+(G3+G4+G5+G6+G7)xG1)/(E3+E4+E5+E6+E7+F3+F4+F5+F6+F7+G3+G4+G5+G6+G7)
If there are seven lines, the result must be equal to
((E4+E5+E6+E7+E8)xE1+(F4+F5+F6+F7+F8)xF1+(G4+G5+G6+G7+G8)xG1)/(E4+E5+E6+E7+E8+F4+F5+F6+F7+F8+G4+G5+G6+G7+G8)
etc...
Here's my Sheets file with the expected results in column J. I'm not at all comfortable with formulas in Sheets.
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;Λ))))))