Multiple scalar products of expanding rows and columns in Google Sheets

user_6944935 0
Viewed 147

I would like to calculate the scalar product of row B1:1 with row B2:2, B3:3, ..., and put the result in column A. My data can have empty cells and is growing both in columns and rows, therefore I would like to find a solution that does not require applying the formula manually to each new data entry.

My current solution mathematically does what I want. However, whenever there are more rows than columns in the sheet, the result is only calculated for the number of columns and the remaining rows remain empty.

=ARRAYFORMULA(TRANSPOSE(MMULT(N(B1:1), N(TRANSPOSE(INDIRECT(CELL("address", B2)&":"&COLUMNS(2:2)))))))

Adding empty columns works for now, but how can I solve this without having to add extra columns all the time?

Example sheet

Result is calculated four times because there are 4 columns from B to E

array-formulas google-sheets-formula google-sheets
1 Answers

You said

I would like to calculate the scalar product of row B1:1 with row B2:2, B3:3, ..., and put the result in column A. ...without having to add extra columns all the time?

In your formula use max(arrayformula(if(B:B<>"",row(B:B),"")))
instead of COLUMNS(2:2)

Your new formula will be

=ARRAYFORMULA(TRANSPOSE(MMULT(N(B1:1), N(TRANSPOSE(INDIRECT(CELL("address", B2)&":"&max(arrayformula(if(B:B<>"",row(B:B),"")))))))))

enter image description here

Useful snippet

How to find the last non-empty row number in a column

=max(arrayformula(if(B:B<>"",row(B:B),"")))

gives the row number of the last non-empty row in column B regardless of empty cells,

enter image description here

EDIT

Please use the following formula to MMULT ANY number of Rows or Columns based on just 2 cells: B1 and B2
(The altered formula (as given by mz1000 in this comment)

=ARRAYFORMULA(TRANSPOSE(MMULT(N(B1:1), 
                         N(TRANSPOSE(INDIRECT(CELL("address", B2)&":"&max(arrayformula(
                            if(INDIRECT(CELL("address", B2)&":"&ROWS(B:B))<>"", 
                                  row(INDIRECT(CELL("address", B2)&":"&ROWS(B:B))),"")))))))))
user_15277800
Related