2

I number myself among several people in the Mmultiverse (c) who use two-dimensional arrays in google sheets, typically to produce a rolling total for a set of data - this is a good example.

It looks as though you can have very large arrays in google scripts according to this, but I can't find documentation for their size in a sheet formula: what is the limit (if any), and what impact would this have on the use of Mmult formulas as above?

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37

2 Answers2

4

10,000,000 (10^7).

The array used in an mmult formula is typically a square array. The impact of the 10 million cell limit on a square 2D array is that you can have sqrt(10^7) rows and the same number of columns. In other words, you can process 3162 rows of data using an mmult technique that produces a square 2D array, but not 3163.

With

=sum(sequence(3162,3162))

enter image description here

With

=sum(sequence(3163,3163))

enter image description here

As noted by @player0, many other combinations of height and width are possible in rectangular arrays from 10m rows X 1 column up to 1 row X 10m columns as long as the total number of cells does not exceed 10m.

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • 1
    I believe your answer is a bit misleading because you are focusing on a perfectly squared 2D array, so there are like 6323 correct answers to your question and it solely depends on how much columns/rows you got in your matrix - https://i.stack.imgur.com/uzxvA.png but ofc agreed on 10^7 part :) – player0 Jan 07 '21 at 00:54
  • You are correct, I meant to say 'square' array - will edit my answer. – Tom Sharpe Jan 07 '21 at 08:05
1

Sheet formulas are only limited by the sheet size itself, which according to the existing Google Drive documentation, is 5 million cells.

Spreadsheets

​1. Up to 5 million cells or 18,278 columns (column ZZZ) for spreadsheets that are created in or converted to Google Sheets.

  1. Up to 5 million cells or 18,278 columns for spreadsheets imported from Microsoft Excel. The limits are the same for Excel and CSV imports.

  2. If any one cell has more than 50,000 characters, that single cell will not be uploaded.

Formulas calculating on this size such as ARRAYFORMULA() would run very slowly, but they will still return values.

CMB
  • 4,950
  • 1
  • 4
  • 16