Array Formula to Automatically Calculate

Question

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

What is the formula on the second line?

indofraiser
– indofraiser

2020-02-19 14:57:42 +00:00
Commented Feb 19, 2020 at 14:57 — indofraiser
– indofraiser, Commented Feb 19, 2020 at 14:57
share a copy of your sheet with fake data

player0
– player0

2020-02-19 15:47:53 +00:00
Commented Feb 19, 2020 at 15:47 — player0
– player0, Commented Feb 19, 2020 at 15:47
@player0 ok, now there is a copy!

Fábio Szamszoryk
– Fábio Szamszoryk

2020-02-19 15:58:34 +00:00
Commented Feb 19, 2020 at 15:58 — Fábio Szamszoryk
– Fábio Szamszoryk, Commented Feb 19, 2020 at 15:58
@indofraiser look, now there is a copy of my sheet.

Fábio Szamszoryk
– Fábio Szamszoryk

2020-02-19 15:59:36 +00:00
Commented Feb 19, 2020 at 15:59 — Fábio Szamszoryk
– Fábio Szamszoryk, Commented Feb 19, 2020 at 15:59

player0 · Accepted Answer · 2020-02-19 16:26:57Z

1

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)))))}

answered Feb 19, 2020 at 16:26

player0

131k14 gold badges91 silver badges149 bronze badges

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

4 Comments

Fábio Szamszoryk Over a year ago

Is it possible I add two more columns beside column "TIPO" with the second and third highest value? If yes, which code can I use? Thanks!

Fábio Szamszoryk Over a year ago

It doesn't work, when I change "select Col 2 to select Col 3" returns a lot of "#VALUE! #VALUE!..." :(

player0 Over a year ago

see: docs.google.com/spreadsheets/d/…

Fábio Szamszoryk Over a year ago

man could you help me with this one? :) stackoverflow.com/questions/69004977/…

Collectives™ on Stack Overflow

Array Formula to Automatically Calculate

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related