CODEWITHSUNDEEP
arraysexcelexcel-formulaarray-formulassumproduct
BoinkingWinkie
BoinkingWinkie

Reputation: 21

Excel (formula only no VBA) Dot Product using MMULT or SumProduct

Fundamental Constraint: I do not want to 'expend' cells to do this and do this complete with 'formulas' not VBA.

Question: Is there a way to use SUMPRODUCT or MMULT function to calculate dot product of two vectors (array). The arrays are not populated in discrete cells. They are going to embedded in some cases as literal numbers within the formula -or- referenced as some function of cell(s) to derive the components of those arrays. The following pseudocode fully depicts what I wish to do.

=**SUMPRODUCT(** MakeArrayFrom(**1.0**, **3.0**, **4.0**) , MakeArrayFrom(**M42**, **X22+N42**, **5.0+B10**) **)**

MakeArrayFrom() is not a VBA function I wish to write, but want a 'native' set of functions that would do a similar thing. I am not trying to code a function. I want to only use any combination of 'native' Excel functions (don't care how convoluted or long it is) to handle the array input.

Again, I do not wish to pre-fill some cells with components of those array before using them. I would like a solution where I would be able to define the contents of the array directly within the formula that uses Array types. Don't want SUMPRODUCT(A1:C1,A3:C3) where an 'array preparation' step must be pre-made.

I tried a number of ways of fooling the Excel system to take components of the array within the confines of the SUMPRODUCT or MMULT ( ) function, to no avail. Hoping someone can guide me and the community of a 'direct' method of setting up an array (again, without utilizing separate cells) that feeds it into the mouth of the Array-Hungry formulas like SumProduct or MMULT.

Upvotes: 1

Views: 92

Answers (1)

BoinkingWinkie
BoinkingWinkie

Reputation: 21

So [BigBen]'s answer of: =SUMPRODUCT({1,3,4},HSTACK(M42,X22+N42,5+B10)) seems to be the key to understanding how to do this.

The HSTACK function is aptly used to fill an array with components that are NOT JUST literal scalars (numbers, strings, etc) -or- contiguous ranges.

This is the rub that no one on the internet seems to even think about addressing. Now that we know about HSTACK, we can probably find some solution out there, but that's because we are armed with this knowledge of this enabling function. [Examples of HSTACK enabling array input][1] [1]: https://i.sstatic.net/cTBfDCgY.png

Upvotes: 1

Related Questions