+ Reply to Thread
Results 1 to 3 of 3

Trendline Equation Error – Wrong “Y” for Given “X”

  1. #1
    Registered User
    Join Date
    02-26-2011
    Location
    Michigan, USA
    MS-Off Ver
    Excel 2007
    Posts
    12

    Trendline Equation Error – Wrong “Y” for Given “X”

    I can't figure out what I'm doing wrong with trendline equation in the attached chart. The graph showing temperature in "Deg F" vs "Lab Data" looks fine. The trendline created with R^2 = 1 also looks great.

    My problem is that Col D, where I have "Calculated Trendline" values (Y) from the trendline equation, are not even close to the "Lab Data" values (X), from which the trendline equation was originally derived! I just can't figure out where my error is in using this formula.

    Thanks for your help. I know I'll kick myself in the butt when I see the solution.
    Attached Files Attached Files

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Trendline Equation Error – Wrong “Y” for Given “X”

    Select K13:M17 and paste this is the formula bar:

    =LINEST(C23:C63,B23:B63^{1,2},, TRUE)

    Don't press Enter; instead, press and hold the Ctrl and Shift keys, then press Enter. You should see

    Please Login or Register  to view this content.
    See Help for LINEST to understand those values. Yes, the coefficients are different than shown for the trendline (they use different methods), but R2 (in K15) is about the same.

    In D23 and copy down,

    =$K$13*B23^2 + $L$13*B23 + $M$13

    ... to see, in part

    Please Login or Register  to view this content.
    Last edited by shg; 04-09-2011 at 01:05 PM.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    02-26-2011
    Location
    Michigan, USA
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Trendline Equation Error – Wrong “Y” for Given “X”

    shg,
    My apologies for the very late acknowledgement of your quick response - got pulled off on another "hot" project.
    Your solution did the trick, and I made other similar trendlines. What is the purpose of the trendline equation given by Excel if it doesn't work? I thought I had plotted these equations before in the distant past, and they've worked.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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