Google Sheets: Arrays: using ARRAYFORMULA() v. { }

Question

Why does this work: =ARRAYFORMULA(LEFT(A2:A13,1)),
but this doesn't: ={LEFT(A2:A13,1)}

ie, using ARRAYFORMULA returns the array: J F M A M J J A S O N D, while using the braces instead of the formula, only returns the J in January.

Answer 1

In Google sheets you need to wrap a function in ARRAYFORMULA if you want to feed an array (in your example the range A1:A12) into an argument of the many functions which by default only accept single values for their arguments (in your example the 'string' argument of the LEFT function) and only output a single value. When you use ARRAYFORMULA in this way the input array is processed element-by-element through the requested function and an output array is generated which has the same dimensions as the input array.

The 'braces' you describe in your example (proper name 'array literals') look like they correspond to the array formula syntax from older versions of Excel, where the array formulas have to be entered using Ctrl+Shift+Enter which wraps the function in the brace characters as shown. Array literals are used in a different way in Google Sheets (to effectively 'glue' different arrays together into one). A full discussion of the complexities of array formulas and array literals (and how their implementations differ in Google Sheets vs old Excel Vs new Excel) would take several pages...

Answer 2

The curly brackets are meant for generating arrays by referring cells, rows, columns, but not to convert formulas into array ones. I understand it's different in Microsoft Excel.

For formulas like that you can use ARRAYFORMULA, BYROW, BYCOL, MAP and INDEX among others in order to process arrays in the order and sequence you need at each opportunity.

Can't give you an exact explanation of why it's like this, but I think it has its logic too. It allows you to have in between curly brackets different kind of processing arrays depending on your needs. Maybe the first column is processed with MAP, and the next ones with BYROW, just for mentioning an example. You can be more specific in the way you need the calculations to be done.

I hope it's useful in some way!