# Google Sheets: average cells in Row 4 up to last filled cell in Row 2

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

Try:

``````=AVERAGE(FILTER(4:4, LEN(2:2)))
``````

and see if that works for you.

## Explanation

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

• 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