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 Mar 24 '15 at 22:29



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 Mar 24 '15 at 22:27
  • @AlE.You are right. Explanation added. – JPV Mar 25 '15 at 9:57
  • @truedson. Glad to hear ! You are most welcome. – JPV Mar 25 '15 at 9:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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