User Defined Formula to Generate Polynomial Regression Equation

[lysychkey]

Hello, all.

I've been searching and attempting to create a user-defined formula (UDF) that returns the equation for polynomial regression without use of an excel chart. What I'm currently doing is typing the formula below into a cell to generate the equation.

=ROUND(INDEX(LINEST(C46:C63,B46:B63^{1,2,3}),1),5) & " * " & K44 & " ^ 3 + " & ROUND(INDEX(LINEST(C46:C63,B46:B63^{1,2,3}),1,2),5) & " * " & K44 & " ^ 2 + " & ROUND(INDEX(LINEST(C46:C63,B46:B63^{1,2,3}),1,3),5) & " * " & K44 & " + "& ROUND(INDEX(LINEST(C46:C63,B46:B63^{1,2,3}),1,4),5)

Because I use this equation of dozens of tables of varying size AND seeing the common and hopefully easily repeatable nature of the equation, I'd like to create a UDF that generates the coefficients and puts it into equation form. For this example, it's cubic regression, so the result of the UFD would produce something akin to ax^3 + bx^2 + cx + d. Here's what I've attempted so far.

• I recorded a macro of typing in the equation to a cell. I copied the macro into a new module and attempted to replace the ranges inside of the LINEST function with user input from the UDF. I tried replacing the ranges directly; I tried transposing the user input and replacing the ranges; I tried converting the user input to a string and replacing the ranges. Nothing worked.
• I searched through several forums to find the answer. The common elements I saw in these forums were transposing the user input ranges, and then using various forms of Application.LinEst(y, Application.Power(x, Array(1, 2))). I could not get these to work either.

Any help is greatly appreciated.

 

[Dave Patton]

What version of Excel do you use?
With the latest version, you can use Let and/or Lambda.

It is often a great help if one posts an extract of their sheet with the forum's tool named XL2BB.
Please also show expected results.

I have no knowledge relating to Polynomial Regressions.

Did you search for formulas,check Excel's advanced and specialized functions, and review Analysis ToolPak?