google spreadsheet array formula vs. paste down best practice

6
votes

best practices question on array formulas in google spreadsheet

say i have a column of value in column A. In column B, I'm multiplying by 10. I can use an array formula for the whole range or make the first individual formula in column B and paste down.

my question is which is better to use? is the array faster to calculate? is there any benefits from a speed point of view? I have big spreadsheets, I was wondering if this would make a difference.

2
google-sheets
After more experience with array formulas, it seems like the benefits are that they are future proof (ie, when you add more cells array formulas automatically adds continue functions), but it seems like the spreadsheet works alot faster without array formulas. Maybe every time values change the array formula has to recalculate everything in that array? versus single formula pasted down only has to recalculate anything that has changed? I was hoping the Google people can comment on this one. - jason

2 Answers

4
votes

I really can't provide any references for this answer, it is based on anecdotal evidence and discussion with other Google Sheets users.

For a relatively simple calculation, like the one in your question, I believe the array solution has better performance for very large data sets. It also mitigates against the possibility of bumping the formula limit in Sheets (40,000, but CONTINUE functions populated by array formulae do not contribute to this count).

However, for very complicated calculations, both options really have the ability to grind the spreadsheet to a halt. Notorious examples of this are array formulae where we need to resort to string manipulation (eg CONCATENATE-ing arrays together, and SPLIT-ting them apart again). In this case, I personally would go to plan C, and write a Google Apps Script custom function, that takes an array (arrays) as an argument (arguments), uses pure Javascript to manipulate the data, and outputs an array.

3
votes

You can kinda test this with two scripts

Multiplying 30,000 rows where each cell is 10 by 10

The Auto expansion takes ~ .275 s The CopyDown ~ .678 s

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

 function AutoExpand() {
 var ss    =SpreadsheetApp.getActive();
 var sheet =ss.getSheetByName('AAA');
 var LC    = sheet.getLastColumn();
 var LR    = sheet.getLastRow();

 var start = new Date();
 //Auto-expanding Formulas to be added
 //Two dim array with 1 row
 var formulas = [["=ArrayFormula(A:A*10)"]];

 //Add auto-expanding formulas to Cell(s)
 var cell = sheet.getRange(1,LC+1,1,formulas[0].length);
 cell.setFormulas(formulas);
 SpreadsheetApp.flush();

 //Get range and post back Display Values
 //var r = sheet.getRange(1,LC+1,LR,formulas[0].length);
 //var v = r.getDisplayValues();
 //r.setValues(v);

 var end = new Date();
 var executiontime = end - start;
 Logger.log(executiontime); 
 }

CopyDown

function CoppyDown() {
var ss    =SpreadsheetApp.getActive();
var sheet =ss.getSheetByName('AAA');
var LC    = sheet.getLastColumn();
var LR    = sheet.getLastRow();

var start = new Date();
//NON Auto-expanding Formula(s) to be added
//Two dim array with 1 row
var formulas = [["=A:A*10"]];

//Add NON auto-expanding formula(s) to Cell(s)
var cell = sheet.getRange(1,LC+1,1,formulas[0].length);
cell.setFormulas(formulas);
SpreadsheetApp.flush();

//Get range FULL Range of Cells W/Formulas
var r = sheet.getRange(1,LC+1,LR,formulas[0].length);

//Add formulas to Row1 Cell(s)
var cells = sheet.getRange(1,LC+1,1,formulas[0].length);
cells.setFormulas(formulas);

//Copy formulas down
cells.copyTo(r);
SpreadsheetApp.flush();

//Get the Display Values of the Range W/Formulas
//var v = r.getDisplayValues();

//Clear the Formulas Before the Postback
//This Super Speeds up the Postback
//r.clear();

//Postback Formulas as Values
//r.setValues(v);

var end = new Date();
var executiontime = end - start;
Logger.log(executiontime); 
}