1. Limited time only! Sign up for a free 30min personal tutor trial with Chegg Tutors
    Dismiss Notice
Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Excel Help

  1. Aug 6, 2014 #1
    Is there a formula that will give me the equation for the line of best fit of a data set, the line being a 6th degree polynomial? I know how to graph the table and add a line of best fit while showing the equation but as far as I know the equation cannot be used in the given format in excel. If there was a way to refer back to the equation for further calculations that would work as well. Would it be easier to use a different program such as Matlab for in-dept data analysis? Thanks.
     
  2. jcsd
  3. Aug 7, 2014 #2

    collinsmark

    User Avatar
    Homework Helper
    Gold Member

    Hello tburke2,

    Welcome to Physics Forums! :smile:

    You might have luck with the Trendline, if that suits your needs.

    • Start by inserting a chart/plot. It could be a Line chart if you are comfortable with your x-axis being the natural numbers. But you'll likely want to create a Scatter Chart instead, so you have control of the x-axis.
    • Once the chart is made, left-click on any point. Now all points are highlighted.
    • Now right click on a point, and a menu appears.
    • Click "Add Trendline..." from the menu's options.
    • Now off to the right, a new menu appears, "Format Trendline."
    • Click the "Polynomial" in the radio-button choices.
    • Select '6' in the "Order" menu (either type it in, or hit the up arrow repeatedly, until it displays '6').
    • Click the checkbox that says "Display Equation on chart"

    If you need to change any of the data, simply modify the original data and the equation will change accordingly.

    On the other hand, if you need to add all new data points it might mean that you must re-select your data. So when originally selecting your data for the x- and y- axis, you might wish to select some unused elements for future data points.
     
  4. Aug 7, 2014 #3

    collinsmark

    User Avatar
    Homework Helper
    Gold Member

    Oh, wait. I think I understand what you would like to do.

    You want to automatically grab the coefficients of the polynomial and insert those into cells in the spreadsheet for future use. Is that right? I'm not sure if that can be done automatically. If it can be done, I'm not sure how. [Edit: Directly, -- taken directly from the chart -- that is. See next post on how you might re-create the coefficients.]

    But if you put the coefficients into cells manually, you can then use those to create a formula. You might find the built-in POWER() function useful along with the '+' and '*' operators.

    Of course, if you ever change your original data (used to create the polynomial) you'll have to change the resulting coefficients manually in your spreadsheet. Again, I'm not sure how to link cells with the Trendline polynomial coefficients to spreadsheet cells automatically (if it can be done at all).
     
    Last edited: Aug 7, 2014
  5. Aug 7, 2014 #4

    collinsmark

    User Avatar
    Homework Helper
    Gold Member

Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook

Have something to add?
Draft saved Draft deleted



Similar Discussions: Excel Help
  1. Excel question (Replies: 1)

Loading...