Multiplying matrices in VBA
 

Why are you reinventing the wheel? LINEST does this.
http://www.stfx.ca/people/bliengme/E...Polynomial.htm

Depending on where your data lives, you can use one of the following
approaches from VBA
http://www.google.com/groups?selm=VA...37cab0%40t8000
http://www.google.com/groups?selm=ue...GP10.phx.gb l

These approaches can be adapted to MMULT and MMINVERSE if you really
want to do it manually.

Jerry

Mark Schreiber wrote:

I am trying to write a VBA User-defined function to develop a

quadratic curve-fit formula. It can be done on the spreadsheet

itself by creating a 3x3 square array of {X, X^2, X^3 ...} terms, and

a 3x1 vector array of {Y, XY, X^2Y} terms, then using the spreadsheet

function {=MMULT(MINVERSE(X_SquareArray),XY_VectorArray)}. This will

yield the coefficients for a quadratic polynomial y=b0 + b1*x +

b2*x^2. However, I have a number of datasets containing (X,Y)

ordered pairs, and don't want to devote a lot of spreadsheet space to

creating these arrays. Instead, I want to do all the matrix

multiplication down to obtaining the coefficients within the

user-defined function. Then the final step will be to set the

function value equal to the polynomial equation, using an input value

for x. Does anyone know how to do this trick?

Multiplying matrices in VBA
 

You're welcome. Help for LINEST has mislead many a user into
undestimating its capabilities.

You can use TRANSPOSE(LINEST()) to get the coefficients in a column. sas

Jerry

Mark Schreiber wrote:

Jerry,
Thanks for the tip. I did not realize you could use LINEST to get the

coefficients for a polynomial by writing the formula using an array of

exponents {=LINEST(y's, x's^{1,2,3})}. I thought it was strictly a

linear regression tool. Not trying to reinvent the wheel, just do

something with a little finesse. Along the way, I did discover that

you can't put the output array of coefficients from LINEST into a

column; they have to be entered into a row since they are just the

first row of the more complete statistical results array. Thanks for

your excellent help.