I have row 1 containing some dates (undetermined number, because end formula will be a dynamic number) and I want row 5 to display the average of every column that has a date as a header.

Trying to have this array formula work :

arrayformula(iferror(
if(B1:1 = "",,
AVERAGE(
indirect(
SUBSTITUTE(ADDRESS(1,COLUMN(B1:1),4),"1","") 
& 7 & ":" & 
SUBSTITUTE(ADDRESS(1,COLUMN(B1:1),4),"1","")
)))))

I think I did isolate the problem in the arrayformula, I can't see where the problem lies with it ?

Here's a screenshot :

and here's the link to the spreadsheet :

https://docs.google.com/spreadsheets/d/1GbIWwv0lq6nVdn4dHkSO0-QqZYEPCwJtntCu8wcSd9Q/edit?usp=sharing

CodePudding user response：

QUERY method:###

=ArrayFormula(IF(B1:1="",,IFERROR(VLOOKUP(COLUMN(B1:1),QUERY(SPLIT(FLATTEN(TRANSPOSE(COLUMN(B7:7)&"♦"&B7:1000)),"♦"),"select Col1,avg(Col2) where Col2 is not null group by Col1 label avg(Col2) ''",0),2,FALSE),)))

MMULT method:

=ArrayFormula(IF(B1:1="",,IFERROR(MMULT(N(TRANSPOSE(ROW(B7:1000)^0)),N(B7:1000))/MMULT(N(TRANSPOSE(ROW(B7:1000)^0)),N(B7:1000<>"")),)))