CODEWITHSUNDEEP
google-sheets
Michael
Michael

Reputation: 13

Google Sheets Array Formula: repeat formula for new responses

I have this formula in one of the columns:

=(MAX(C2:F2)-MIN(C2:F2))/AVERAGE(C2:F2)

Currently, I am manually updating this formula column every time a new response has been added from Google Forms. I tried using the ARRAYFORMULA, but I cannot get it to populate the new rows.

  1. Can someone point me in the right direction on how to repeat the formula for new entries?
  2. Is it possible to email the recipient back with the newly calculated column value after they have submitted the Google Forms?

Upvotes: 1

Views: 63

Answers (1)

player0
player0

Reputation: 1

={"ARRAY FX"; ARRAYFORMULA((QUERY(TRANSPOSE(QUERY(TRANSPOSE(C2:F), 
 "select "&REGEXREPLACE(JOIN( , IF(LEN(C2:C),
 "max(Col"&ROW(C2:C)-ROW(C2)+1&"),", )), ".\z", "")&"")), "select Col2")-
 QUERY(TRANSPOSE(QUERY(TRANSPOSE(C2:F), 
 "select "&REGEXREPLACE(JOIN( , IF(LEN(C2:C),
 "min(Col"&ROW(C2:C)-ROW(C2)+1&"),", )), ".\z", "")&"")), "select Col2"))/
 QUERY(TRANSPOSE(QUERY(TRANSPOSE(C2:F), 
 "select "&REGEXREPLACE(JOIN( , IF(LEN(C2:C),
 "avg(Col"&ROW(C2:C)-ROW(C2)+1&"),", )), ".\z", "")&"")), "select Col2"))}

0

Upvotes: 1

Related Questions