Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

Posted on 2011-09-27
Medium Priority
776 Views
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.
0
Question by:allelopath
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 5
• 4
• 3
• +2

LVL 12

Expert Comment

ID: 36709804
no direct function but this should solve the purpose:
http://www.bettersolutions.com/excel/EAV113/LT721811311.htm
0

LVL 27

Expert Comment

ID: 36710019
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

LVL 1

Author Comment

ID: 36710040
In this case, 105 points
0

LVL 1

Author Comment

ID: 36710062
like so
points.xls
0

LVL 27

Expert Comment

ID: 36710210
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

LVL 81

Accepted Solution

byundt earned 2000 total points
ID: 36710299
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

LVL 1

Author Comment

ID: 36710336
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

LVL 81

Expert Comment

ID: 36710387
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

0

LVL 27

Expert Comment

ID: 36710392

``````Doing a rough fit by eye:

Doing a rough fit by eye:

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

LVL 37

Expert Comment

ID: 36710404
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

LVL 37

Expert Comment

ID: 36710457
Here's a graph showing what the data of a best-fit quadratic would go compared to your data.

0

LVL 27

Expert Comment

ID: 36710505
Needs some work, but it's a start.
0

LVL 27

Expert Comment

ID: 36710531
0

LVL 37

Expert Comment

ID: 36710681
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

LVL 1

Author Comment

ID: 36710803
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst youâ€™ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to dâ€¦
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will diâ€¦
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
###### Suggested Courses
Course of the Month5 days, 10 hours left to enroll