Trendline Not The Same as Equation Displayed

I plotted data from lab measurements on an Excel chart, 
then asked the program to create a trendline for the data 
and display the equation for the trendline on the graph. 
The only problem is that the equation Excel produced is 
completely different than the trendline shown.

The trendline is a 5th order polynomial for the data set:
x
20
25
30
35
40

y
194
85
57
43
31

The equation displayed is:
y = 0.0037x4 - 0.4927x3 + 24.728x2 - 552.38x + 4705

The equation transposed to spreadsheet:
=(0.0037*A2^4)-(0.4927*A2^3)+(24.728*A2^2)-(552.38*A2)+4705

where A2 is the x value. The plot of this equation is not 
the same as the trendline given.
Anybody got a clue to this discrepancy?

0
anonymous (74722)
1/27/2004 10:18:16 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
480 Views

Similar Articles

[PageSpeed] 16

Dan -

You probably need more significant digits in the trendline. Select the 
trendline, press Ctrl-1 (one) to format it, and on the number tab, 
select a Scientific format with 14 digits.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Dan wrote:

> I plotted data from lab measurements on an Excel chart, 
> then asked the program to create a trendline for the data 
> and display the equation for the trendline on the graph. 
> The only problem is that the equation Excel produced is 
> completely different than the trendline shown.
> 
> The trendline is a 5th order polynomial for the data set:
> x
> 20
> 25
> 30
> 35
> 40
> 
> y
> 194
> 85
> 57
> 43
> 31
> 
> The equation displayed is:
> y = 0.0037x4 - 0.4927x3 + 24.728x2 - 552.38x + 4705
> 
> The equation transposed to spreadsheet:
> =(0.0037*A2^4)-(0.4927*A2^3)+(24.728*A2^2)-(552.38*A2)+4705
> 
> where A2 is the x value. The plot of this equation is not 
> the same as the trendline given.
> Anybody got a clue to this discrepancy?
> 

0
jonNOSPAM (60)
1/27/2004 11:52:32 PM
You don't have enough data to fit anything beyond a 4th order polynomial.

In addition to John's suggestion to display enough figures on the chart 
to be meaningful, you could use the LINEST worksheet function.  If the x 
data are in A2:A6 and the y data are in B2:B6, then the array formula
   =LINEST(B2:B6,A2:A6^{1,2,3,4})
would calculate the polynomial coefficients.

In Excel versions prior to 2003, the trendline (with enough displayed 
figures) tends to be more accurate than LINEST.  For this problem, the 
LINEST coefficients are accurate to 8 figures, and the trendline 
coefficients are accurate to 12.

Jerry

Dan wrote:

> I plotted data from lab measurements on an Excel chart, 
> then asked the program to create a trendline for the data 
> and display the equation for the trendline on the graph. 
> The only problem is that the equation Excel produced is 
> completely different than the trendline shown.
> 
> The trendline is a 5th order polynomial for the data set:
> x
> 20
> 25
> 30
> 35
> 40
> 
> y
> 194
> 85
> 57
> 43
> 31
> 
> The equation displayed is:
> y = 0.0037x4 - 0.4927x3 + 24.728x2 - 552.38x + 4705
> 
> The equation transposed to spreadsheet:
> =(0.0037*A2^4)-(0.4927*A2^3)+(24.728*A2^2)-(552.38*A2)+4705
> 
> where A2 is the x value. The plot of this equation is not 
> the same as the trendline given.
> Anybody got a clue to this discrepancy?

0
post_a_reply (1395)
1/28/2004 1:00:32 AM
Reply:

Similar Artilces:

Display Julian Dates in Outlook 2002
Is there any way to have the outlook calendar display the Julian date in outlook 2002? Either in addition to the Gregorian or just the Julian. Any help would be appreciated. If you would like to email the solution (if there is one) to: Joseph.R.Longoria@kcc.com I would appreciate it. ...

ImagingFactory CE5: Memory leak with displaying bitmaps?
Hello, Program memory is getting low using the following code (VS 2008 Windows CE 5 device) which displays a bitmap. In a loop - sometimes 4k of memory is missing permanently. What is the reason for this leak? Thanks in advance. .... CoInitializeEx(NULL, COINIT_MULTITHREADED); SUCCEEDED(CoCreateInstance (CLSID_ImagingFactory,NULL,CLSCTX_INPROC_SERVER,__uuidof(IImagingFactory),(LPVOID *)&pImgFactory)); .... void CCeImgTstDlg::DrawImage(WCHAR * szBitmapName) { IImage * pImage = NULL; if (pImgFactory) { if (pImgFactory->CreateImageFromFile(sz...

RE: Still not displaying the dialog box
Hi All, 1. Machine and OS specifications I have Windows XP running Office XP Standard Edition. These software packages are installed on my dell Inspiron laptop. I have 128 MB RAM with a 40 GB HardDrive. 2. Problem In excel, when I pressed the New menu item, a dialog box would appear displaying all of the known templates. I tried the fix listed below, however it did not fix my problem. Is there a registry setting I should set? Dmoss, Did you try my solution of yesterday? If it didn't work, look through the File category (Customize) for another New button. -- Earl Ki...

Display answer only in another cell of one containing a formula
I have a a cell containing a formula to display a total time. e.g. A1 contains =SUMIF($E$5:$E$25,$E28,$D$5:$D$25). This is my actual formula. The answer it gives is 01:00 in time format. Is it possible to display the answer to the formula in cell B1 but not to contain the formula. B1 needs to change as A1 does. type in B1 =INDIRECT("A1") still B1 will contain some formula but may not be the formula of A1. however as the formula in A1 changes the value in B1 also will change. is this what you want?? Mally <Mally@discussions.microsoft.com> wrote in message news:7510...

Address Display
I have set up a little invoice database and things are going quite well. My only issue is the display of the address. Each line of the address is stored in a table separately (this is important for other things I need to do with the data). My problem is with how this is displayed on the invoice report. If I give each text field its own line, then when there is no data in say COName or recipAddress3 I get an ugly blank space. Same thing with the city/state/zip line: if I leave enough space for the city name, i have to push state and zip all the way off to the right, even if the city field is...

Display notes on tab (NotesCount)
Hello CRM-community, a new version of the client side customization NotesCount for CRM 4.0 is available. Some new features like type dependend counters within the notes tab and a tooltip function for more details are now implemented. I created a codeplex project for centralized maintenance and collaborative bug tracking... so as it was for CRM 3.0 in 2007 it's completely free and open-source at http://notescount.codeplex.com/. I hope you'll like it. Comments are gratefully appreciated. Thomas Hi Thomas Brilliant Thank you Pete Thomas Roschinsky wrote: > Hello CRM-commun...

parameters of trendline in two cells ?
Dear ng, for the following data, x y 3,19 4,4 2,91 246 2,76 4686 I show an exponential trendline in the graph: y = 4,7775E22e-1,5923E+01x But how can I put the obtained parameters in two cells ? Now I type them, but I would like to refer to their values. Thanks a lot in advance for your suggestions, Marcel I do not match your multiplier of 4,7775E22. Perhaps the data you posted is rounded? There are three options for getting the estimates in a worksheet. 1. Copy the values from the equation on the graph (you should format them t...

smtp address display
Hi all, i have an issue after an upgrade from 5.5 to 2003, there is an 'unsubscribe' public folder against which a program runs to unsubscribe senders from a mail out. This program can't deal with the 'friendly names' displayed in the emails which are coming in from people wishing to unsubscribe from the newsletter, it's expecting to see smtp addresses in the 'from' field of the emails arriving in the public folder. Any ideas?? Is there a way of changing this behaviour so that just the smtp address of the sender is displayed in the 'from' field? Andre...

Display Calendar full screen
Is there any way that I can display the data part of my outlook calendar full screen? I'd like a view that just shows me appointments etc without any toolbars, status bars etc at all. Thanks Neil NeilH <n.hall@nospamvirgin.net> wrote: > Is there any way that I can display the data part of my outlook > calendar full screen? I'd like a view that just shows me appointments > etc without any toolbars, status bars etc at all. Something like this might appeal: http://www.carthagosoft.net/desktask.htm -- Brian Tillman ...

Outlook2007 automation problem with ".Display"
I am using automation in a VB5 program to send Emails using Outlook2007. If I use the ".Send" mailitem the email is sent out Immediately. If I use the ".Display" mailitem in my program, after I look over the Email and then click the "Send" button on the Outlook display screen, the email is placed in my outbox but does not get sent out. I have to exit my program and then click "Outlook". When Outlook displays, I can see the Email in the Outbox which will then automatically go out (without me having to click send and receive). I want th...

displaying a chart in a form
How do I display an excel chart in a form? cd, Add an image control to the form. Copy the chart. Paste directly (Ctrl + V) into the "Picture" property of the image in the properties window. Adjust the "Picture Alignment", "PictureSizeMode" properties to suit. Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "cd" <deycd@hotmail.com> wrote in message news:1135274950.930389.39040@g43g2000cwa.googlegroups.com How do I display an excel chart in a form? I should have said I need the chart to refresh when the data ref...

HELP ! WebFolder display no resource
Hi, I have a big trouble with my WebDav Server Implementation: - I use Windows XP SP2 - My server work with litmus-0.10.4 - MS Office Work BUT WebFolder doing a PROPFIND display no folder and respond that the folder is not available. It use to work with WebFolders but may after some chage of my code ??? it doesn't work. I don't understand. -> It's working with NetDrive, DavExplorer -> Same error with WebDAVPlot HELP ! Best Regards The WebFolder Request: ********************** [SpyFilter] - =================================== [SpyFilter] - Headers [SpyFilter] - --------...

Displaying a 12-digit Number
How can I have a cell display a 12-digit number? Currently, when you would type the number 123456789012, 1.23457E+11 is displayed. As I need to have the spreadsheet saved as a Text Delimited file, even when formatting the cell as text does not work, because when the file is being converted, it sees the cell as a number, and brings me back to square one. Any ideas?? Thank you. Hi format the cell with the custom format 000000000000 -- Regards Frank Kabel Frankfurt, Germany John wrote: > How can I have a cell display a 12-digit number? > Currently, when you would type the numbe...

adding promotional pics in the net display
i have been taking the file microsoft made for the net display and trying to add promotional jpegs on the top. i throw it in dreamweaver and it looks good in there but when i take the file to the register it doesnt show the pics... i did save copies of the jpegs in the image file in html...dont know what is wrong...please help... ...

Chart Display
Hi I have about 5 thousand rows of information with the following: Column A - Date Column B - Time Column C - Level Column D - Velocity Column E - Total I have created a radar chart to read this information but I cannot get the X axis to change - I am trying to change the scale to show me every 12 hours or 24 hours. Basically, the times are very close because the machines are checked every 5 minutes so I have a tick mark every 5 and want to increase that. Is this possible? Thanks in advance Ann (Ireland) .. ...

displaying nonsqure window?
Hello, I'd like to write application where user interface is some gnome with tooltips etc, not regular window. Is this possible with mfc ? where I could fins some example? thanks MFC is written on top of Win32. Win32 allows you to have a odd shaped window. So you can do so in MFC as well. There are no MFC classes to do this if thats what you are asking. However you can happily use MFC with Win32 and therefore have a irregular shaped window in MFC application. Take a look at SetWindowRgn. Here is a sample at codeproject: http://www.codeproject.com/dialog/SimpleIrregular.asp ------- ...

Outlook display
In Outlook when you change view from messages with auto preview to unread messages - unread shows up in bold but it still shows all messages. Have no idea how it got this way. Trying to get unread messages to only show unread messages. ...

Displaying normal screen widths on wide-screen monitor
How do I display normal screen widths on a wide-screen monitor? I replaced a normal-width monitor with a wide-screen monitor, and now everything is stretched abnormally wide. How do I simulate a normal-width screen on this wide-screen monitor, so that stuff looks normal again? Stuff will not look normal until you adjust your video cards output to match the native resolution of the widescreen monitor. right click an empty spot on desktop/properties/settings and then move the little slider till the numbers match you monitors native resolution. Is that better?? peter --...

Displaying Days of the Week
Does anyone know if there is a way to not display Today, Yesterday, Wednesday, Last Week, Last Month etc etc in the Inbox and Folders in Outlook. All I need to see is the Date which already displays. Thanks, PJ View, Arrange by, UNcheck Show in groups. pj wrote: > Does anyone know if there is a way to not display Today, Yesterday, > Wednesday, Last Week, Last Month etc etc in the Inbox and Folders in Outlook. > All I need to see is the Date which already displays. > > Thanks, PJ ...

excel display alert
When using a macro to edit and replace an existing file, Excel displays an alert prompting the user to select Yes, No, or Cancel to replace that file. No is the default (black background) button. I would like to change the default button to Yes. Any ideas? Thanks Why bother asking the user ? You can stop the alert with Application.DisplayAlerts = False (reset to True later). and force your own Save or SaveA -- Message posted from http://www.ExcelForum.com ...

command button caption does not display
I can't get the caption to actually show up on my command button on my form. Any ideas on why not? Note: the size of the caption is not the issue -- I have a huge button and the caption is single letter with font size 8. Note: The presence of a picture is not the issue. The picture property of the caption is none. JGpatrick, A wee bit more information would help to trobuleshoot your issue... a. Access version? b. Font used? c. If any code is being used to set the Caption property? -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied criti...

Adding Filtering to a Rating Equation?
I am using the following formula to determine the individual rating of a group of players running a race. F3=CEILING((COUNT($A$3:$A$23)+1-RANK(E3,$E$3:$E$23,1))/(COUNT($A$3:$A$23)/5),1) A B C D E F G H 2 ID. No. Time #1 Time #2 Time #3 Average Time Rating Gender Level 3 6 01:01.1 02:59.6 01:59.9 02:00.2 5 Male U14 4 5 03:00.0 01:01.1 02:24.7 02:08.6 5 Male U14 5 16 03:02.8 02:47.7 01:01.1 02:17.2 5 Male U14 6 4 02:24.7 01:59.9 02:32.6 02:19.1 5 Male U14 7 17 02:25.2 02:11.3 02:47.4 02:28.0 4 Male U14 8 19 02:15.6 02:21.2 02:47.7 02:28.2 4 Male U14 9 11 01:59.9 02:25.8 03:01.9 ...

Attachments do not display in preview pane
Does anyone know what would cause the following behavior in Outlook?: We are using Exchange 5.5 and Outlook 2000 clients. Users are emailing an Excel file (but this happens with any type of file) to our purchasing manager with their orders. Some of the attachments will show up with the file in the Preview Pane and some of them will only display the paper clip in the upper right of the header of the preview pane. Is this a user setting somewhere? I have looked in Outlook but could not find any option. I have also tried using Word as the editor and not using Word as the email editor but that ...

Why does Custom Format not control Cell Display?
XL 2003 Have a cell which shows in the formula bar: 06/10/2005 10:10:32 AM In the worksheet (same cell but unselected) the display is: 38513.4239814815 No matter what I do with cell formating i.e. general to text to custom (BTW which is mm/dd/yyyy h:mm) the cell displays 38513.4239814815 UNLESS I select the cell - then it displays 06/10/2005 10:10:32 AM? When I de-select the cell it reverts back to 38513.4239814815 I thought that I knew better! What is the very obvious thing that I am missing? Dennis Maybe you're looking at formulas... Tools|options|view tab Uncheck Form...

display comment based on cell value
Hi All, I know about conditional formatting - I was wondering if there is a way to display text based on a value - without using an IF formula? Any ideas. Thanks in advance. Hi where do you want do display the text?. If you want this text to be included in the same or adjacent cell you have to use VBA (e.g. the worksheet_change event). To give an example: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub On Error GoTo CleanUp: With Target If .Valu...