+ Reply to Thread
Results 1 to 2 of 2

Excel Trendline and Equation Application – wrong results

  1. #1
    Registered User
    Join Date
    03-02-2013
    Location
    Manila,Philippines
    MS-Off Ver
    Excel 2011
    Posts
    24

    Excel Trendline and Equation Application – wrong results

    For the sale of production units, I have a excel table with two type of entries:
    Number of units sold in a year (axis X) and unit price (axis Y).
    When the number of units sold increase, the unit price decrease (see table and graph).

    I did a scatter chart (smooth marked scatter) and added a trendline over the chart . I selected different types of trendlines (LOG, polymonial or power types) and displayed the equation on the chart for each type.

    My goal is to compute thanks to the provided equation, the unit price for 15 units sold in a year.

    I did the computation for 3 different choices of trendline types with the equation formula provided in option for each type, but the result are not correct at all.

    The result should be in the range of 1,170,000 as can be seen on the graph. The results calculated with the equation formulas are far from correct…

    What could be the reason? Thank you!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Excel Trendline and Equation Application – wrong results

    Common "rounding" mistake. You are using the default "whatever Excel feels like" number formatting, which means, that you are seeing some parameters to only 1 or 2 significant figures. For example, the 3E6 that you are using for the polynomial trendline is really 2.562579e6 a difference of almost half a million.

    Solution: format your trendline labels to show more digits (I tend to use scientific notation with no less than 6 decimal places), then copy the more precise numbers.

    Solution 2: All of the trendline equations can be represented linearly and regressed directly in the spreadsheet using the LINEST() function. If this is something you will be doing frequently, I would recommend this. Then there is no loss of precision in the copy step, because there is no copy step. https://support.office.com/en-us/art...a-fa7abf772b6d
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ 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. Linear trendline:wrong equation
    By Jan M. in forum Excel Charting & Pivots
    Replies: 13
    Last Post: 09-28-2016, 02:09 PM
  2. Replies: 5
    Last Post: 12-02-2013, 12:27 PM
  3. Excel prints wrong trendline equation on graph
    By justyna-whitney in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-19-2013, 06:13 PM
  4. Replies: 2
    Last Post: 08-13-2012, 09:22 AM
  5. Trendline Equation Error – Wrong “Y” for Given “X”
    By billexchry in forum Excel General
    Replies: 2
    Last Post: 04-15-2011, 01:15 AM
  6. Excel 2007 trendline equation
    By kamelkid2 in forum Excel General
    Replies: 2
    Last Post: 04-05-2011, 11:01 AM
  7. Replies: 3
    Last Post: 03-06-2011, 01:38 PM
  8. Excel graphed trendline does not match derived equation
    By Keith in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 03-13-2006, 04:20 PM

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