quadratic equation and linest

[gregbog]

Dear friends,
I am using the LINEST formula in order to get the coefficients (a,b,c
of a quadratic equation y = ax^2+bx+c.
The syntax I use is the following
Suppose that my data are in A2:a11 (x values) and B2-B11 (y values). T
find the parameters a, b and c, I select three cells in a row, ente
=LINEST(B2:B11;A2:A11^{1;2}) and complete the formula wit
CRTL+SHIFT+ENTER since it is an array formula.

I am now looking for two things:
a) How can I get the r or the r2 value of this equation (of cours
without having to graph the data)? Can I do it using LINEST?

b)How can I get the coefficients a, b and c of the same quadrati
equation when I want the line to pass from zero (i.e. intercept = zero
?

I would appreciate any help!
Gre

 

[Dana DeLouis]

Hello. You need to add True to the Stats option of linest.
Select an output area 5 rows, by 3 columns, and Array enter the following:

=LINEST(B2:B11,A2:A11^{1,2},,TRUE)

Your R^2 value will be in the 3rd row of the output, in the first column.

If you don't want all the information, here is a small example that just
extracts the R^2 value: (Array formula)

=INDEX(LINEST(B2:B11,A2:A11^{1,2},,TRUE),3,1)

HTH
Dana DeLouis.

 

[Ron Rosenfeld]

Dear friends,
I am using the LINEST formula in order to get the coefficients (a,b,c)
of a quadratic equation y = ax^2+bx+c.
The syntax I use is the following
Suppose that my data are in A2:a11 (x values) and B2-B11 (y values). To
find the parameters a, b and c, I select three cells in a row, enter
=LINEST(B2:B11;A2:A11^{1;2}) and complete the formula with
CRTL+SHIFT+ENTER since it is an array formula.

I am now looking for two things:
a) How can I get the r or the r2 value of this equation (of course
without having to graph the data)? Can I do it using LINEST?

b)How can I get the coefficients a, b and c of the same quadratic
equation when I want the line to pass from zero (i.e. intercept = zero)
?

I would appreciate any help!
Greg

All of your questions are answered in HELP for LINEST.


--ron

 

[gregbog]

Dear Dana, thank you very much for your help
You too Ron,
My last (I hope) question about linest is how can I get th
coefficients a, b, c....etc for higher order polynomials (higher tha
2nd degree).
For the second degree polynomial (y = ax^2+bx+c), I am using LINES
formula as follows: (my data are in A2:a11 x values, and B2-B11
values):

=LINEST(B2:B11;A2:A11^{1;2})

At first I thought that LINEST can only give you information abou
linear functions (y=ax+b), but a friend told me to use "^{1;2}" in my
values, and this did the job for a second order polynomial. However,
dont really know what this does (I am not a mathematician), and thus
can not modify it to get the coefficients for a 3rd (or higher) orde
polynomial.
I would appreciate if someone could explain this to me.
Many thanks:

 

[Dana DeLouis]

Hello. For your second degree polynomial, you are selecting 3 horizontal
cells and Array entering the following equation:

=LINEST(B2:B11;A2:A11^{1;2})

To find a three degree polynomial using this technique, just select 4
horizontal cells (for all 4 coefficients), and array enter the following.
Make it {1,2,3,4} for a 4 degree poly, etc. Note that I am using US
settings. You may need to change some "," to ";" for yours to work.

=LINEST(B2:B11,A2:A11^{1,2,3})

HTH
Dana DeLouis

 

[gregbog]

Thanks a million Dana

Gre