Linking the Regression equation of a line to a table

C

Ch1ck3n Fock3r

I am using 3 points to create a line, and the trendline utility to display
the display the equation of a line on the graph. I am trying to create a
table that automatically calculates values based on the equation of that
line.
Rough description:
x-axis values range from 0.25 to 1.00
Y-axis values range from 1000 to 15000

If the regression equation is y = -20200*x^2 + 5686.7*x + 7482.2, I want to
be able to create a table in which Column A ("x" values) ranges from 0.25 to
1.0, and column B automatically utilizes the regression equation to insert
"y" values. The regression equation changes, so I need the values in Coulmn
B ("y" values) to automatically update as the equaiton changes.

A B
1 0.25
2 0.26
3 0.27
4 0.28
5 0.29
6 0.3
7 0.31
8 0.32
9 etc,etc.

Currently, I am forced to manually input the regression equation into a
table each time it changes to re-calculte the needed "y" values in that
table. For the equation y = -20200*x^2 + 5686.7*x + 7482.2

A B C D
1 -2.02E+04 5686.7 7482.2 x-values
2
3
4 -7031 1.00
5 -6686 0.99
6 -6345 0.98
7 -6008 0.97
8 -5675 0.96
9 -5346 0.95
etc etc etc

So, I have to create a formula that reads =($A$1*D4^2)+($B$1*D4)+$C$1
--Example is for Cell A4, however is dragged into every cell in Column A.
As the equation of the line changes, I am forced to manually enter the
values in A1, B1, and C1 to have the table refresh.

I hope my question makes sense
 
J

Jerry W. Lewis

You don't need to write a VBA program for this; simply use the LINEST function
=LINEST(ydata,xdata^{1,2}) will fit a quadratic.

Jerry
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top