Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing two matrices | Excel Discussion (Misc queries) | |||
inverse matrices | Excel Discussion (Misc queries) | |||
matrices | Excel Discussion (Misc queries) | |||
Combining certain matrices | Excel Discussion (Misc queries) | |||
transpose matrices | Excel Programming |