Matrix multiplication involving the TRANSPOSE of a row

Question

I'm using Excel 2010. My example is in a spreadsheet with 1 in A1; 2 in B1, 1 in D1; 2 in D2. When I multiply a row by a column, everything works fine:

=MMULT(A1:B1,D1:D2)

If I use the TRANSPOSE function to multiply a row by itself and put the answer in a single cell, it chokes (saying #VALUE!):

=MMULT(A1:B1,TRANSPOSE(A1:B1))

This seems to be because it wants to produce the same answer twice. Selecting two cells, typing the formula in the top one then linking them with CTRL-SHIFT-ENTER (which adds curly brackets to the formula) gives the correct answer twice:

Out of curiosity, I tried =MMULT(A1:B1,TRANSPOSE(TRANSPOSE(D1:D2))). It also wanted to repeat the answer.

(screenshots of all five examples are together here)

While I realise I can just copy the duplicated answer to a scrap area and then collect a single cell from there, that seems a bit messy. And, more seriously, I'm worried about whether other strange things happen when matrix functions are chained an Excel.

Is there a way to suppress the duplication so multiplying one row by one column gives one answer, or have I misunderstood everything?

Solution

You have to transpose the matrix first in the worksheet and then multiply the original matrix with the transpose as you have done in

=MMULT(A1:B1,D1:D2)

This gives the correct result without any duplication. Using the transpose function inside the mmult either chokes or creates duplicate if you select multiple cells.