google sheetsgoogle-sheets-arrayformula
I am using Google sheets withe the following formula in one of the columns
=ARRAYFORMULA(IF(ISBLANK(M2), "", ROUND(M2/G2, 2)))
Whenever I add a new row anywhere in the sheet, the formula does not get copied and I have to use other means to copy the formula in each cell of the inserted row.
How should I be writing this formula so that it automatically works for newly inserted row?
Use a custom function.
function result(range) {
var splitCol = 1; // split on column B
var output2 = [];
for(var i=0, iLen=range.length; i<iLen; i++) {
var s = range[i][splitCol].split("\n");
for(var j=0, jLen=s.length; j<jLen; j++) {
var output1 = [];
for(var k=0, kLen=range[0].length; k<kLen; k++) {
if(k == splitCol) {
output1.push(s[j]);
} else {
output1.push(range[i][k]);
}
}
output2.push(output1);
}
}
return output2;
}
The script evaluates each row, and particularly the second column of each row (in JavaScript arrays are zero based, so column 2 corresponds to index 1 of the array). It splits the contents of that cell, into multiple values and uses the "\n" as delimiter (line feed). After that it adds the existing info to an array and only add the individual results, when it hits index 1 (k == 1). The newly prepared row is then added to another array, that's being returned to show the result.
data
result
I've created an example file for you: multi-line cells into new rows.
In array formulas instead of AND and OR functions use * and + respectively.
AND: Instead of
=ArrayFormula(and(A2:A4,B2:B4))
use
=ArrayFormula(A2:A4*B2:B4)
OR: Instead of
=ArrayFormula(or(A2:A4,B2:B4))
use
=ArrayFormula(A2:A4+B2:B4)
Best Answer
When using array formulas, they only know to which range they should apply calculations if you include that information. It can be confusing, since conditional formatting formulas will apply an array given just the top cell in the column range.
Try this in place of your current formula:
=ARRAYFORMULA(IF(ISBLANK(M2:M), "", ROUND(M2:M/G2:G, 2)))