Solving Parabolic Curve in Excel: Find A with Given X and Y

[marlon]

Hi i have a question on excell...

How can a fit a parabolic curve when all x and y values are given in a column. What i mean is given the equation y = A(x-7)² and ten x with corresponding y values, what is the best apprximation for A ?

regards
marlon

 

[imabug]

if you have all the values, you could just plot it out and have Excel plot out the trendline for you. Right click a data point on the graph, select Add Trendline and select Polynomial (make sure the order is set to 2). Make sure to tell Excel to display the equation on the graph (under the options tab). You should get a second order polynomial which you can re-write to find out what A is.

 

[lalbatros]

Hi Marlon

first: you decide what you take as best approximation, let's assume chi² deviation w/r to the theoretical curve
second: you put in a cell the unknown parameter(s), with some guess value
third: you calculate in a new cell the chi² value
fourth: you use the excel solver to minimize the chi² by changing the parameter(s)

The xl solver can solve for up to 200 variables, in the standard version.

Another method:
Find out the analytical theory that gives the parameter(s). You can do that by minizing analytically the chi².
You can also find that in many textbooks.
For one parameter this will be quite simple.
For several parameters you come on a system of linear equations that you can solve also in Excel.
(use minverse and mmult functions).
For several parameters, the coefficients of the matrix are some moments of the distribution of the experimental x and y values.

Yet another method:
Your exact problem is actually a linear regression (apparently 1 parameter: A, maybe more).
The variable of the linear regression is not x but (x-7^)² in your example.
Excel offers a very powerful linear regression function called "LINEST".
It will solve your problem and it will provide additional statistical results.