Stew Corman or scorman -

(1) Investigate the array-entered LINEST worksheet function for obtaining
regression coefficients (either in worksheet cells or in a VBA routine).

(2) Observe the usual cautions about overfitting the data (which might occur
using a fourth-order polynomial).

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel



"scorman" wrote in message
...
On Feb 27, 9:20 am, "Dana DeLouis" wrote:
Don't know if you would be interested in this general idea. It has no
error
checking.
You have to move the "Tbl" code to one line in vba. (Broken up for
posting)
Usually, Tbl refers to a group of cells on a worksheet.

FunctionraQuestion(SalesGrowth As Double, Tier As Integer) As Double
Dim Tbl
Tbl = [{0,0,0,0;
0.03,0.001,0.002,0.003;
0.05,0.002,0.006,0.008;
0.10,0.03,0.01,0.013;
0.15,0.04,0.014,0.018;
0.20,0.05,0.02,0.025}]

raQuestion = WorksheetFunction.VLookup(SalesGrowth, Tbl, Tier + 1)
EndFunction

--
Dana DeLouis

Dana,
I too am looking for a "general idea" so perhaps you can enlighten me,
since I have a different and perhaps more complicated math problem
involving arrays of data. (BTW ..I do not know VBA, but Fortran
subroutines I can understand)

To keep it simple, I have a three variable data set and I have
successfully used TREND to fit a fourth order polynomial in a two way
regression.

I would like to have a UDF, which passes two variables and returns the
third.
Is it possible to incorporate the existing xls TREND functions into
the UDF or am I asking too much?

a link to my "clumsy" spreadsheet is he
http://www.otherpower.com/images/sci...urbine_RE_.xls

each of the 3 "xxx polar" sheets are replications to return the
varible "Cl" or "Cd" from the input variables in red "AOA" and RE#"
all the original data is on left side of those polar sheets

I am presuming that to use VBA, I would have to input the actual math
formulas to do the polynomial regression which is far from trivial??

Any assistance would be appreciated, or the final answer that it
simply cannot be done.
TIA,
Stew Corman from sunny Endicott