0

I have an excel sheet with some column data that I would like to use for some matrix multiplications using MMULT-function. For that purpose I need to reshape the column data first. I would like to do the reshaping using a dynamic array function since that could then feed directly into the MMULT function without having to actually display the reshaped matrix in the sheet (i.e. keeping only the column with the input data visible for the user). I am aware of ideas such as the one outlined here http://www.cpearson.com/excel/VectorToMatrix.aspx however however as far as I can see that requires having the reshaped data displayed in the sheet which I do not want. An alternative could be to enter the arrays directly in the formula using curly brackets, however as far as I can see this notation does not allow cell-references, i.e. something like MMULT({A1,A2,A3;A4,A5,A6},{A7,A8;A9,A10;A11,A12}) is not allowed. Any ideas for solving this issue?

An example is shown below, basically I have the column-data in my sheet, but do not want to repeat the data (as reshaped data), however, I would still like to be able to do display the square of the reshaped matrix.

Reshaped data and matrix multiplication: enter image description here

Improve this question
9
  • 3
    You haven't given details of the reshaping you require. A small example dataset with expected result would help greatly. Commented Jun 27, 2022 at 8:15
  • Have a look at WRAPCOLS(). And possibly BYROW() to replace tedious MMULT(), however I still don't see what the expected end-result should be. Commented Jun 27, 2022 at 8:44
  • Hi Jos, I have shown an example above, basically what I want to avoid is displaying the reshaped data in the sheet, but using the reshaped matrix as input for the MMULT function. I.e. something like MMULT({B3,B4,B5;B6,B7,B8;B9,B10,B11},{B3,B4,B5;B6,B7,B8;B9,B10,B11}) to calculate the square of the reshaped matrix. Commented Jun 27, 2022 at 8:45
  • Hi JvDV, WRAPCOLS seems to be exactly what I am looking for, unfortunately I do not have this function available, My excel version is "Microsoft® Excel® for Microsoft 365 MSO (16.0.14326.21008) 64-bit ". Commented Jun 27, 2022 at 8:50
  • 1
    @JvdV That's just matrix multiplication: 30=(1x1)+(2x4)+(3x7), i.e. first row by first column. Commented Jun 27, 2022 at 9:29

1 Answer 1

Reset to default
1

For reshaping a 9x1 array into a 3x3 array:

INDEX(B3:B11,SEQUENCE(ROWS(B3:B11)/3,3))

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.