Google Sheets : horizontal (row) conditional Arrayformula

Question

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

idfurw · Accepted Answer · 2022-03-27 07:44:35Z

2

`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<>"")),)))

edited Mar 27, 2022 at 7:44

answered Mar 24, 2022 at 7:52

idfurw

5,8582 gold badges7 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Michel Over a year ago

ty it works ... but ... It's painfully slow, especially for a simplified test page : I did several tests and it takes about 7 seconds to update. I'm afraid it might get too slow on the production file. I will make some more tests. Thanks for your help !

Michel Over a year ago

it takes about 10 seconds to update the production sheet. I hope someone comes with a faster solution. Do you think my approach is wrong and cannot be used with somewhat slight corrections / modifications ? I will either use it if the delay is acceptable for the users, or if no one has a better solution, make it a script.

idfurw Over a year ago

Added another method, please check.

Michel Over a year ago

ty : I will need to understand what you're doing here :-) It works well, but it doesn't work into the production sheet, so I tried to duplicate the test sheet (same data exactly in the same reference cells) and the exact same formula, but also get a blank result, with no error message. I'm puzzled ...

Michel Over a year ago

Edit : a pure duplicate of the sheet produces the expected result, but rebuilding another sheet with the same data and same formula in the same cells doesn't work.

|

Collectives™ on Stack Overflow

Google Sheets : horizontal (row) conditional Arrayformula

1 Answer 1

`QUERY` method:

`MMULT` method:

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

QUERY method:

MMULT method:

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related

`QUERY` method:

`MMULT` method: