+ Reply to Thread
Results 1 to 4 of 4

Using Polynomial Equation from Trend Line

  1. #1
    Registered User
    Join Date
    07-11-2011
    Location
    England
    MS-Off Ver
    Office 2019
    Posts
    58

    Using Polynomial Equation from Trend Line

    Morning guys, I have a problem I'm stuck solving!

    See attached for example worksheet (the yellow cell is the one to change and freetype).

    Basically, I have 3 known values (sq meter) and an associated cost. When you enter a free type sqm in the yellow cell, the result is a value from the polynomial equation along the trendline of the known £ values.

    My problem is this - if I were to increase the known values to a set amount, or by a percentage, the polynomial equation fails, as does the result as the formula is now wrong!!

    Is there a way to actively use the poly formula so that it can update other cells if the formula were to change?? I use a similar system for other areas to the 5th power, but now face the same situation whereby I need to increase the known amounts!

    Many thanks guys,
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,642

    Re: Using Polynomial Equation from Trend Line

    Select 3 cells in one row (say D4:F4)
    Write formula and commit it as array one (Ctrl+Shift+Enter) (make sure - all 3 cells are selected, not just one and formula copied):
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    You will get your polynominal parameters.
    Use them in B8:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    And that's it - now once you change something in A5:B7 it is reflected not only in graph but also in D4:F4 and of course in B8 :-)
    Attached Files Attached Files
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    07-11-2011
    Location
    England
    MS-Off Ver
    Office 2019
    Posts
    58

    Re: Using Polynomial Equation from Trend Line

    That's amazing Kaper, thankyou. I've heard of linest but was never sure how it can used in my own work. Is this easy to expand to 5, 6, 7 known values? Is it just a case of adding them in, or would it amend the formula (=D4*A8^2 +E4*A8 +F4) for example?

    Thanks again

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,642

    Re: Using Polynomial Equation from Trend Line

    if you want to have second order polynominal fitted - only adjust ranges, like
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    if higher order - select more cells and use longer list of powers, like selected 6 cells and
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    for the fifth order polynominal

    and then of course the second formula will be something like:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Trend line polynomial function
    By FFpotaters in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 09-02-2014, 03:44 PM
  2. find the trend-line coefficients for a 5th order polynomial
    By screaming13 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-05-2014, 09:33 AM
  3. forecasting data points through polynomial trend line
    By cjmonks in forum Excel General
    Replies: 1
    Last Post: 06-27-2011, 05:42 PM
  4. Trend Line - Equation
    By Rich 80105 in forum Excel General
    Replies: 3
    Last Post: 06-03-2006, 04:10 AM
  5. 4th order polynomial trend line coefficients
    By RJS in forum Excel General
    Replies: 3
    Last Post: 03-30-2005, 11:36 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1