0

I want to perform the following matrix manipulation in Excel, where a and b are scalars and A and B are arrays (let's say of equal dimension for simplicity), within a single line:

(aA)(bB)

Doing just AB is simple with MMULT, but I can't see how to do the scalar multiplication, short of creating it naively in a fresh set of cells, and plugging that into the MMULT - doing e.g. a*A10:B20 or whatever just gives an error.

Bonus if it's also possible to fit the addition ((aA)(bB) + cC) into a single line.

Alistair
  • 31
  • 2
  • 3
  • Look at [this](https://stackoverflow.com/a/22628830/4717755) and [this](https://stackoverflow.com/q/36947805/4717755) and [this](https://stackoverflow.com/a/36925326/4717755) for a way to do this using VBA. Or if you're looking for a formula solution, consider using [`SUMPRODUCT`](https://stackoverflow.com/a/30775913/4717755) – PeterT Aug 25 '18 at 22:43
  • SUMPRODUCT produces an inner product of two matrices, not a multiplication of one matrix by a scalar. – Alistair Aug 25 '18 at 23:48
  • It would help us help you if you provided a [mcve] complete with expected results rather than abstract references. –  Aug 25 '18 at 23:53
  • Can you give an example where using MMULT with one or more scalars "just gives an error"? Using Scott Craner's posted example, I can just as easily perform: =MMULT(4*A1:B2,3*D1:E2) (without CSE). – XOR LX Aug 26 '18 at 12:00

3 Answers3

0

Since an Excel Formula is bound to one cell with one single value and your result would be a matrix with many values, I see no way in doing this without ExcelVBA. Even then it would look like = mult(A1;B2:D4) for the scalar multiplication after you defined the corresponding function in VBA. This would be possible. But because you mentioned AB is no problem (I don't no how, but anyway) ... why not use the diagonal matrix with your scalar as value? Maybe this helps.

Beduine
  • 1
  • 3
  • Look into array formulas. Excel formulas CAN do matrices, arrays and all that jazz. – vacip Aug 25 '18 at 23:41
  • The diagonal matrix would work - however suddenly 4 scalars turn into 4 4*4 matrices (in this case) - it's just lots of extra cells to worry about, that I'd rather avoid if possible. – Alistair Aug 25 '18 at 23:46
0

INDEX will return an array that you can then put into MMULT:

=MMULT(INDEX(A1:B2*4,,),INDEX(D1:E2*3,,))

As an array formula it must be confirmed with Ctrl-Shift-Enter

The second rows of numbers is just proof that the formula works by manually doing the multiplication.

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
0

You can do this with a simple array formula:

{=MMULT(4*A1:B2,3*D1:E2)+7*A4:B5}

Array-entered (Control-Shift-Enter) into 4 cells (select the 4 cells and then array-enter the formula)

Charles Williams
  • 23,121
  • 5
  • 38
  • 38