Excel: quadratic equation

Does excel have a quadratic equation function?
I have a set of points that I would like to fit ax^2+bx+c=0 though.
LVL 1
allelopathAsked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
Assuming that your x values are the numbers 1 through 106, then your quadratic formula might be:
y = -0.000514x2 + 0.048557x - 0.657532
R² = 0.695230


I array-entered the following formula in a 5 row x 3 column block of cells:
=LINEST(B2:B107,A2:A107^{1,2},TRUE,TRUE)
Among other things, it returns the coefficients a, b, c      for ax^2 + bx + c)

I also made a scatter plot
pointsQ27343514.xls
0
 
viralypatelCommented:
no direct function but this should solve the purpose:
http://www.bettersolutions.com/excel/EAV113/LT721811311.htm
0
 
d-glitchCommented:
How many points do you have?  You need three points to determine a parabola.

If you have more than three points, you can use linear regression in Excel to solve for A, B, and C.
You have to set your data up in a table.  

For each data point:   x² is the coefficient of A     x is the coefficient of B      1 is the coefficient of C
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
allelopathAuthor Commented:
In this case, 105 points
0
 
allelopathAuthor Commented:
like so
points.xls
0
 
d-glitchCommented:
You have have a 105 values.  What do they mean?  What is x?

Why do you think this data should be fit to a quadratic?

These values are measurements of  ?????  varying with respect to ?????.
0
 
allelopathAuthor Commented:
d-glitch:
my apologies, the file I attached had only the y values. The x values should have been 0 - 105.
byundt, however, went the extra mile and figured it out.
0
 
byundtCommented:
If x values are 0 to 105, then the R squared will be the same, but the coefficients will be slightly different:
y = -0.000514x2 + 0.047529x - 0.609489
R² = 0.695230


Brad
0
 
d-glitchCommented:

Doing a rough fit by eye:

     Doing a rough fit by eye:

          -(60 - X)²                -X²      3X
    Y  =  ----------  +  0.5    =   ---  +  ----  -  8.5
            400                     400      10

Open in new window

0
 
TommySzalapskiCommented:
I seriously doubt that a quadratic is a good fit for your data. Should the data really start dropping rapidly with no lower bound as x gets higher?
0
 
TommySzalapskiCommented:
Here's a graph showing what the data of a best-fit quadratic would go compared to your data.
 best fit
0
 
d-glitchCommented:
Needs some work, but it's a start.
ExEx-Quadratic.pdf
0
 
d-glitchCommented:
TommySzalapski is way ahead here....
0
 
TommySzalapskiCommented:
I assumed byundt did a proper best-fit method to get his function and just used it. The point is that I am unsure that a quadratic is the right choice as d-glitch mentioned earlier. I repeat his questions here:
"These values are measurements of  ?????  varying with respect to ?????."
0
 
allelopathAuthor Commented:
TommySzalapski:
You have no way of knowing this, but project the data beyond 106 doesn't make sense.
The quadratic fits well enough to suit my purposes.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.