Solved

Excel: quadratic equation

Posted on 2011-09-27
15
749 Views
Last Modified: 2012-05-12
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
Comment
Question by:allelopath
  • 5
  • 4
  • 3
  • +2
15 Comments
 
LVL 12

Expert Comment

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

Expert Comment

by:d-glitch
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

by:allelopath
ID: 36710040
In this case, 105 points
0
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
LVL 1

Author Comment

by:allelopath
ID: 36710062
like so
points.xls
0
 
LVL 27

Expert Comment

by:d-glitch
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

by:
byundt earned 500 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

by:allelopath
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

by:byundt
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


Brad
0
 
LVL 27

Expert Comment

by:d-glitch
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

Open in new window

0
 
LVL 37

Expert Comment

by:TommySzalapski
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

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

Expert Comment

by:d-glitch
ID: 36710505
Needs some work, but it's a start.
ExEx-Quadratic.pdf
0
 
LVL 27

Expert Comment

by:d-glitch
ID: 36710531
TommySzalapski is way ahead here....
0
 
LVL 37

Expert Comment

by:TommySzalapski
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

by:allelopath
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Calculation 4 52
Excel Split Employee Name into Lname Fname Mname 3 17
Excel VBA 30 41
Loop with matrices. Please help me! 16 42
We are taking giant steps in technological advances in the field of wireless telephony. At just 10 years since the advent of smartphones, it is crucial to examine the benefits and disadvantages that have been report to us.
Article by: Nicole
This is a research brief on the potential colonization of humans on Mars.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question