Quadratic Coefficients

**Ian Adamson** · 07-18-2011, 09:25 AM

I have a series of x and y values for which I am trying to find quadratic coefficients for the equation.

I can get these values off the trendline if I plot a graph, however I want to get them via an equation so I can use them in other equations. This way the input information can be changed and still give equation coefficients without having to take them off a graph.

How do I do this? I can't find any function on excel what will do it. If it can't does anyone know a method I can use to calculate these?

Regards

Ian

**6StringJazzer** · 07-18-2011, 09:31 AM

Excel can provide coefficients for a linear regression, but not quadratic. I think you can use the same feature to do quadratic regression but you have to set your data up a little differently. I'm a little rusty on this but might be able to help if you attach your workbook and explain your data.

You need to have the Data Analysis Toolpak installed, which comes with Excel 2003 but you may not have installed it.

Go to Tools, Data Analysis..., and select Regression. Enter your parameters, and select "New Worksheet Ply:" The feature will dump the resulting parameters of the regression in that location, including x coefficient and b.

**Ian Adamson** · 07-18-2011, 10:35 AM

Thats very helpful. Thank you.

Ian

**MrShorty** · 07-18-2011, 10:41 AM

I prefer to use the LINEST function. Something like =LINEST(known_y's,known_x's^{1,2})

**sweep** · 07-18-2011, 10:46 AM

Hi,

You can extract quadratic constants without the analysis toolpack.

The following, entered as arrays (CTRL, SHIFT and ENTER) will give a, b, and c respectively.

=INDEX(LINEST(known_ys,known_xs^{1,2}),1)
=INDEX(LINEST(known_ys,known_xs^{1,2}),2)
=INDEX(LINEST(known_ys,known_xs^{1,2}),3)

eg

=INDEX(LINEST(E1:E10,A1:A10^{1,2},1)
=INDEX(LINEST(E1:E10,A1:A10^{1,2},2)
=INDEX(LINEST(E1:E10,A1:A10^{1,2},3)

**Jeffrey.Eisele** · 04-04-2022, 04:13 PM

Hi,

This works when the data is arranged in columns, but I need it to work with data arranged in rows. See attached file: Column vs. Row.xlsx

**6StringJazzer** · 04-04-2022, 05:18 PM

Originally Posted by Jeffrey.Eisele

This works when the data is arranged in columns, but I need it to work with data arranged in rows. See attached file: Column vs. Row.xlsx

Administrative Note:

Welcome to the forum.

We are happy to help, however while you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original. Also note that this thread is 10 years old.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

**Ino** · 05-07-2022, 10:02 AM

Hi, sweep,

Thank you very much for this. I've been looking everywhere at how to compute a, b, and c by just using simple formulas like these.

Can you also show how to compute the r-squared from the quadratic equation? (by just using a simple excel formula)

**6StringJazzer** · 05-07-2022, 10:06 AM

Originally Posted by Ino

Can you also show

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread