Is it possible to write a Formula in Google Sheets which calculates the average of the cells in Row 4 but only up to the last filled cell in Row 2?
and see if that works for you.
- FILTER(4:4, LEN(2:2))
This part filters row 4 so that it has the same amount of cells as the non-blank cells in row2. Whether a cell (in row 2) is blank or not is checked with the len function, that will return 0 (no LENGTH) for empty cells.
- AVERAGE(FILTER(4:4, LEN(2:2)))
The above function is wrapped in an average-function...