Array Formula to Automatically Calculate

1
votes

I want an array formula to use with Google Forms data to automatically calculate running metrics on my data.

In this case, in column BF I want it to auto-calculate whenever new responses come in.

I'm trying to use this formula below but isn't working for all column, just for the first line.

=ArrayFormula(If(isblank($BF$3:$BF);"";(INDEX($C$1:$BD$1;0;MATCH(MAX($C3:$BD3);$C3:$BD3;0)))))

My Google Sheet

What I'm doing wrong?

My Google Sheet:

https://docs.google.com/spreadsheets/d/1iD75djqdY8qtClEHpKUEvAS55bP0ARv9AnDUz9mFBuc/edit?usp=sharing

1
if-statementgoogle-sheetsmaxarray-formulasgoogle-sheets-query
What is the formula on the second line? - indofraiser
share a copy of your sheet with fake data - player0
@player0 ok, now there is a copy! - Fábio Szamszoryk
@indofraiser look, now there is a copy of my sheet. - Fábio Szamszoryk

1 Answers

1
votes

delete range BE:BF

paste this in BE1:

={"MAX"; ""; ARRAYFORMULA(QUERY(TRANSPOSE(QUERY(TRANSPOSE(C3:BD); 
 "select "&TEXTJOIN(","; 1; IF(A3:A<>""; 
 "max(Col"&ROW(A3:A)-ROW(A3)+1&")"; ))&"")); 
 "select Col2"; 0))}

paste this in BF1:

={"TIPO"; ""; ARRAYFORMULA(IF(A3:A="";;TRIM(TRANSPOSE(QUERY(TRANSPOSE(
 IF(C3:BD=QUERY(TRANSPOSE(QUERY(TRANSPOSE(C3:BD); 
 "select "&TEXTJOIN(","; 1; IF(A3:A<>""; 
 "max(Col"&ROW(A3:A)-ROW(A3)+1&")"; ))&"")); 
 "select Col2"; 0); C1:1; ));;99^99)))))}

0