Share via

Transpose #¡Value! Error

Anonymous
2019-02-07T12:44:33+00:00

I am triying trying to use the function TRANSPOSE into an MMULT function to obtain a single value, just one figure.

However I only get a result if I expand the formula to have the same dimension as the matrix I am using TRANSPOSE with.

My formula looks like:

=MMULT(MMULT([Weights],[CoVar Matrix]),TRANSPOSE([Weights])

Where Weights is a 1x10 matrix and CoVar Matrix a 10x10 matrix, so the final result must be just one figure (1x1).

Any help please?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Vijay A. Verma 104.9K Reputation points Volunteer Moderator
2019-02-07T14:42:57+00:00

Enter this formula as Array Formula i.e. not by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.

Was this answer helpful?

5 people found this answer helpful.
0 comments

Answer accepted by question author

Vijay A. Verma 104.9K Reputation points Volunteer Moderator
2019-02-07T14:56:17+00:00

Great! I am glad that worked! If you don't have any more questions, feel free to choose a rating and have a great day!

Was this answer helpful?

1 person found this answer helpful.
0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-02-07T14:54:47+00:00

    Great!! Problem solved!

    Thanks Vijay

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2019-02-07T14:14:41+00:00

    Hi Vijay,

    That does not seem to be the problem.

    I am multiplying the resulting 1x10 by the transpose of [Weights], which is a 10x1. So I should get a one figure value.

    Moreover, If I extend the formula to the dimension of the transpose (thats it 10 rows and 1 column) I see the correct numerical result. But I need to have it in just one cell, not in 10 cells.

    Thanks!

    Was this answer helpful?

    0 comments
  3. Vijay A. Verma 104.9K Reputation points Volunteer Moderator
    2019-02-07T12:55:36+00:00

    Hi GuillemPerc,

    I am Vijay, an Independent Advisor. I am here to work with you on this problem.

    MMULT([Weights],[CoVar Matrix]) will give you an array of 1x10, (The result is an array with the same number of rows as array1 and the same number of columns as array2.)

    You are doing MMULT of this 1x10 with Weights which is 1x10 again. For MMULT, the number of columns in Array1 which is 10 here should be same as number of rows in Array2 which is 1 here. Hence, the error.

    MMULT function - https://support.office.com/en-us/article/mmult-...

    Do let me know if you require any further help on this. Will be glad to help you.

    Was this answer helpful?

    0 comments