2

While playing around with array formulas, "sometimes" when the result has more rows and columns than the previously available in the sheet, Google Sheets automatically adds the row needed. I said "sometimes" because I don't figured out yet why sometimes Google Sheets automatically add new rows but other times don't.

To make things worst, like when using MMULT with open ended references (A:A), the recalculation time takes minutes.

Let say that I have a formula with open ended references like the following

=ARRAYFORMULA(MMULT(N(ROW(A2:A)>=TRANSPOSE(ROW(A2:A))),SIGN(ROW(A2:A))))

If the sheet has 5,000 rows the result should have 25,000,000 cells. Sometimes it just returns the following error message:

Error
The resulting array was too large.

Other times, Google Sheets add a lot of rows, and the recalculation time could take minutes.

Besides using close ended references (A1:A10) instead of open ended references(A:A), Is there a way to tell to Google Sheets to do not insert rows or columns?

4

Short answer

Use ARRAY_CONSTRAIN(array,row_limit,column_limit)

Explanation

ARRAY_CONSTRAIN function could return the number of rows and columns that you want, just set the row_limit and column_limit parameters accordingly. To automatically set this parameters you could use a function like COUNT or COUNTA.

To calculate the number of rows/columns considering the data range by using built-in functions,

for rows use a formula like the following

=MAX(IF(LEN(A2:A)>0,ROW(A2:A)-1,0))

for columns use a formula like the following

=MAX(IF(LEN(1:1)>0,COLUMN(1:1)-1,0))

The sample formula in the question will look like the following:

=ARRAYFORMULA(
   ARRAY_CONSTRAIN(
     MMULT(N(ROW(A2:A)>=TRANSPOSE(ROW(A2:A))),SIGN(ROW(A2:A))),
     MAX(IF(LEN(A2:A)>0,ROW(A2:A)-1,0)),
     MAX(IF(LEN(1:1)>0,COLUMN(1:1)-1,0))
   )
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.