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?

  • I'm sure some sample data would be useful.
    – ale
    Commented Mar 24, 2015 at 22:29

1 Answer 1




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...

  • 1
    This would be an awesome answer with some explanation of how the functions work.
    – ale
    Commented Mar 24, 2015 at 22:27
  • @AlE.You are right. Explanation added.
    – JPV
    Commented Mar 25, 2015 at 9:57
  • @truedson. Glad to hear ! You are most welcome.
    – JPV
    Commented Mar 25, 2015 at 9:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.