I have some entries against days of the year:

I want to state the average of these entries for every week (hence the week helper column in Column A).

Normally to do this I would use FILTER:

AVERAGE(FILTER(C:C, A:A = A1 ) ))

Where A1 is the week number against which I would like to place the average (assume that this is on another tab and correctly referencing the data).

This works for a single cell, and if I drag the formula down manually. However, I want it to update automatically, but I cannot use ARRAYFORMULA with FILTER. I am not sure of the solution: the other questions on SO can be solved more easily as they do not require multiple rows to be returned. I do, so I cannot use VLOOKUP, which would solve the problem.

CodePudding user response：

in a new cell, on a brand new tab, try this assuming the tab name with the data is "Sheet1":

=QUERY('Sheet1'!A:C,"select A,AVG(C) where A<>'' group by A")

Here is a good resource on QUERY().