Error in formula displayed for linear and 2nd order curve fits in Excel 2003

I have created trendlines for some pretty simple data using both 2nd
order polynomial and linear fits.  In both cases, the displayed
formulas have their 1st and 2nd order coefficients wrong by a factor
of 10!! (I have read lots of posts about increasing precision but this
is not what I am up against).  Has anyone seen this?
0
6/19/2008 3:38:36 PM
excel.charting 18370 articles. 0 followers. Follow

4 Replies
2922 Views

Similar Articles

[PageSpeed] 28

What version of Excel? Could you include the data in a follow up post (not 
as an attachment), along with the coefficients you've computed?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


<richard.wiens@rogers.com> wrote in message 
news:720f5b1e-06ee-4e46-895b-637f17c33d6a@m73g2000hsh.googlegroups.com...
>I have created trendlines for some pretty simple data using both 2nd
> order polynomial and linear fits.  In both cases, the displayed
> formulas have their 1st and 2nd order coefficients wrong by a factor
> of 10!! (I have read lots of posts about increasing precision but this
> is not what I am up against).  Has anyone seen this? 


0
jonxlmvpNO (4558)
6/19/2008 3:54:16 PM
I am using Office 2003 Standard, SP3

Here is the data for the linear trend:

Density           0.1           0.2         0.3
0.4             0.5           0.6
Throughput     3.125	    2.726	2.378	2.061	1.742	1.452

The trendline formula displayed is y=3D-.3324x+3.4107

If use either SLOPE or just do a simple manual (delta y/delta x) slope
calcualtion, you get -3.324

For the 2nd order trend here is the data:

Density           0.1           0.2         0.3        0.4
Throughput      6.27	     4.9	   3.75	2.87

The formula displayed is y =3D 0.1225x^2 - 1.7475x + 7.8975.  Using
another stats package I did a curve fit and got y=3D12.225x^2 - 17.475x
+ 7.8975, which yeilds correct ys for the given xs.  Interestingly,
the 2nd order coefficient seems to be out by a factor of 100 (10^2),
while 1st order seems to be off by a factor of 10 (10^1).

Richard



On Jun 19, 11:54=A0am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
wrote:
> What version of Excel? Could you include the data in a follow up post (no=
t
> as an attachment), along with the coefficients you've computed?
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. -http://PeltierTech.com
> _______
>
> <richard.wi...@rogers.com> wrote in message
>
> news:720f5b1e-06ee-4e46-895b-637f17c33d6a@m73g2000hsh.googlegroups.com...
>
>
>
> >I have created trendlines for some pretty simple data using both 2nd
> > order polynomial and linear fits. =A0In both cases, the displayed
> > formulas have their 1st and 2nd order coefficients wrong by a factor
> > of 10!! (I have read lots of posts about increasing precision but this
> > is not what I am up against). =A0Has anyone seen this?- Hide quoted tex=
t -
>
> - Show quoted text -

0
6/19/2008 4:34:00 PM
Make an XY chart, not a line chart. A line chart counts categories as 1, 2, 
3, etc., regardless of the numeric values in the cells.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


<richard.wiens@rogers.com> wrote in message 
news:e39d65e2-d8b4-4ca2-a3dd-dc057d0f47fd@h1g2000prh.googlegroups.com...
I am using Office 2003 Standard, SP3

Here is the data for the linear trend:

Density           0.1           0.2         0.3
0.4             0.5           0.6
Throughput     3.125     2.726 2.378 2.061 1.742 1.452

The trendline formula displayed is y=-.3324x+3.4107

If use either SLOPE or just do a simple manual (delta y/delta x) slope
calcualtion, you get -3.324

For the 2nd order trend here is the data:

Density           0.1           0.2         0.3        0.4
Throughput      6.27      4.9    3.75 2.87

The formula displayed is y = 0.1225x^2 - 1.7475x + 7.8975.  Using
another stats package I did a curve fit and got y=12.225x^2 - 17.475x
+ 7.8975, which yeilds correct ys for the given xs.  Interestingly,
the 2nd order coefficient seems to be out by a factor of 100 (10^2),
while 1st order seems to be off by a factor of 10 (10^1).

Richard



On Jun 19, 11:54 am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
wrote:
> What version of Excel? Could you include the data in a follow up post (not
> as an attachment), along with the coefficients you've computed?
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. -http://PeltierTech.com
> _______
>
> <richard.wi...@rogers.com> wrote in message
>
> news:720f5b1e-06ee-4e46-895b-637f17c33d6a@m73g2000hsh.googlegroups.com...
>
>
>
> >I have created trendlines for some pretty simple data using both 2nd
> > order polynomial and linear fits. In both cases, the displayed
> > formulas have their 1st and 2nd order coefficients wrong by a factor
> > of 10!! (I have read lots of posts about increasing precision but this
> > is not what I am up against). Has anyone seen this?- Hide quoted text -
>
> - Show quoted text -


0
jonxlmvpNO (4558)
6/19/2008 4:46:01 PM
On Jun 19, 12:46=A0pm, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
wrote:
> Make an XY chart, not a line chart. A line chart counts categories as 1, =
2,
> 3, etc., regardless of the numeric values in the cells.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. -http://PeltierTech.com
> _______
>
> <richard.wi...@rogers.com> wrote in message
>
> news:e39d65e2-d8b4-4ca2-a3dd-dc057d0f47fd@h1g2000prh.googlegroups.com...
> I am using Office 2003 Standard, SP3
>
> Here is the data for the linear trend:
>
> Density =A0 =A0 =A0 =A0 =A0 0.1 =A0 =A0 =A0 =A0 =A0 0.2 =A0 =A0 =A0 =A0 0=
..3
> 0.4 =A0 =A0 =A0 =A0 =A0 =A0 0.5 =A0 =A0 =A0 =A0 =A0 0.6
> Throughput =A0 =A0 3.125 =A0 =A0 2.726 2.378 2.061 1.742 1.452
>
> The trendline formula displayed is y=3D-.3324x+3.4107
>
> If use either SLOPE or just do a simple manual (delta y/delta x) slope
> calcualtion, you get -3.324
>
> For the 2nd order trend here is the data:
>
> Density =A0 =A0 =A0 =A0 =A0 0.1 =A0 =A0 =A0 =A0 =A0 0.2 =A0 =A0 =A0 =A0 0=
..3 =A0 =A0 =A0 =A00.4
> Throughput =A0 =A0 =A06.27 =A0 =A0 =A04.9 =A0 =A03.75 2.87
>
> The formula displayed is y =3D 0.1225x^2 - 1.7475x + 7.8975. =A0Using
> another stats package I did a curve fit and got y=3D12.225x^2 - 17.475x
> + 7.8975, which yeilds correct ys for the given xs. =A0Interestingly,
> the 2nd order coefficient seems to be out by a factor of 100 (10^2),
> while 1st order seems to be off by a factor of 10 (10^1).
>
> Richard
>
> On Jun 19, 11:54 am, "Jon Peltier" <jonxlmv...@SPAMpeltiertech.com>
> wrote:
>
>
>
> > What version of Excel? Could you include the data in a follow up post (=
not
> > as an attachment), along with the coefficients you've computed?
>
> > - Jon
> > -------
> > Jon Peltier, Microsoft Excel MVP
> > Tutorials and Custom Solutions
> > Peltier Technical Services, Inc. -http://PeltierTech.com
> > _______
Well that explains it perfectly (including my observation about the
coefficients)!

Doesn't seem intuitive to me that a line chart would do that but at
least now I know.

Thanks.

Richard



> > <richard.wi...@rogers.com> wrote in message
>
> >news:720f5b1e-06ee-4e46-895b-637f17c33d6a@m73g2000hsh.googlegroups.com..=
..
>
> > >I have created trendlines for some pretty simple data using both 2nd
> > > order polynomial and linear fits. In both cases, the displayed
> > > formulas have their 1st and 2nd order coefficients wrong by a factor
> > > of 10!! (I have read lots of posts about increasing precision but thi=
s
> > > is not what I am up against). Has anyone seen this?- Hide quoted text=
 -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

0
6/19/2008 5:15:13 PM
Reply:

Similar Artilces:

Help with a formula please.
In column "A" I have about 400 file names with each path, for example: D:\Folio_02\AL605_02.xls Each file contains a value named "ATT" and another named "XBB". I need to get these values in adjoining columns. I've tried every variation of the following I can think of without success: A1&"!"&ATT None have worked. I'm sure I have done this before but can't remember and can't find any of my old files with an example. Please help. Jim More like: A1&"!ATT" ************ Anne Troy www.OfficeArticles.com "jim...

Only Supplier Items WHen entering a Purchase Order
When creating a new PO, it would be most helpful to only list the items purchased from a particular vendor rahter than every item in the database. Several of our cutomers have requested this. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Bus...

Setup of Generic Out of office Message office 2003/2007
Hi, Out Communications department want's us to setup a standard out of office message for all mailboxes in the organisation. They want every user to have access to the generic Out of Office message. Is there a way I can create an Outlook template or something that I could give all users to put in their mailbox Out of Office area. thanks -- Kath ...

Excel Progrom Error
Hi there Happy new year to all. I often receive the folowing error message in a box titled "Program Error". EXCEL.exe has generated errors and will be closed by Windows. You will need to restart the program. An error log is being created. The only option is OK which closes Excel. I do not know where the error log is created. I am using Office 2000 Service Pack 3 with Windows 2000 Professsional Service Pack 4. Can anybody throw some light on this error. Thanks in anticipation. Michael What are you doing at the time (i.e. is it consistent)? -- Regards; Rob ----------------...

Sharebuilder account import error
Invalid .qif header error when trying to import .qif file from sharebuilder accounts. Anyone know of a fix or experienced this problem?? I typed a long mail to sharebuilder regarding this issue...still waiting fora reply. Hopefully they will be able to help. Microsoft flat out told me it wasn't their issue and to contact my broker! No help AT ALL. Microshaft..... OMalley ...

Storing Data in Microsoft Excel
Hi I wonder if anyone out there would help I have excel, and I have some data which I update daily in the spreadsheet, I would like to create a macro, to copy all the data onto another page, and then, so that the next day I enter new set of data and when I press the same button it copies it all to the same page as the other data, but one row underneath (so that it doesn't override it) I know this can be done, but I cant remember how to do it. I did something in VB, active.offset but that's all I can remember if there is anyone that could help that would be brilliant All the be...

2003 Keeps the default workbook open
When I start Excel, it starts up with a blank workbook. If I immediately open a saved file, it closes the blank workbook... All good... except mine has stopped closing the blank one. So I always end up with 2 workbooks open, the one I opened and a blank one that I'm not interested in! Is this a setting? if so where? Can I fix it? thanks... M Did you or someone create a default workbook (ie, Book.xltx or Book.xltm) and save it in "C:\Users\YourUsername\AppData\Roaming\Microsoft\Excel\XLSTART"? On 5/9/2010 4:17 AM, Michelle wrote: > When I start...

Need help w/ excel formula (duplicate entry)
Hello, I am not sure if this is possible but I would like write a formula that would recognize a duplicate entry under same column. e.g., column A has heading of Sample ID# and under is sample ID# in random order. If an entry of ID is then made but if it is a repeat of the ID that was previously entered, I would like to it to give some kind of warning message. Thank you. You may want to read some of Chip Pearson's techniques for dealing with duplicates: http://www.cpearson.com/excel/duplicat.htm Visit Chip Pearson's site for a Data|validation solution: http://www.cpearson.com/ex...

DPM2010 RC Errors on secondary server.
We have recently upraded our 2007 SP1 Primary DPM server to 2010 RC and it is working perfectly. I've set up a brand new, fresh Windows 2008 R2 Server for the secondary server, and have told it to protect one of the protection groups on the primary server. Everything works for a little while, but every half hour or so, the DPM service crashes and says: "Connection to the DPM service has been lost. Review the application event log for information about a possible service shutdown. ID: 917" Then asks to restart the administrator console. This kills the ...

Help with Formula Please
I have the following formula in cell c1 in a spreadsheet: =IF(D1>A1,IF(B1="over","+"),IF(D1<A1,IF(B1="over","-",IF(D1<A1,IF(B1="under","+"),IF(D1>A1,IF(B1="under","-")))))) 200 over - 195 200 over + 205 200 under FALSE 205 200 under + 195 What I've done above is test the formula for all four possible outcomes. As you can see the third outcome in the table (the fourth argument in the formula) is false, but I want it to read "-". I have changed the formula around every way I can thi...

Problems with Using Query with Analyze it with Microsoft Excel
We have a problem with Access 2003 and EXCEL 2003. I have a QUERY from ACCESS 2003 and hit the following sequence of commands - "TOOLS" "OFFICE LINKS" and "ANALYZE IT WITH MICROSOFT EXCEL". I notice the Excel pops up and I see fields being imported and when it is down the Excel spreadsheet is blank - not even a blank spreadsheet comes up. What am I doing wrong. We need Access 2003 to link to the data in Excel so we can edit and modify it using EXCEL as well as ACCESS. Thanks for your help. JW ...

ERROR
Hi I am using a spreadsheet in Excel 2002 on Windows XP, for some reason the account numbers (which someone input, along with addresses) have a little green mark in the corner of the cell. I thought it was to show that there is a comment, but it is not. When you click on the cell, a little warning sign shows, saying ERROR - number in this cell formatted as text or preceded by an apostrophe. When I click on the arrow by the warning sign, it says Number stored as text Convert to number Ignore error Edit in formula bar Error checking options Show formula in audinting toolbar Now I looked a...

Change organisation name in Exchange 2003
Hi, We are migrating our XCH 5.5 to XCH 2003. We need to change the organisation name. We can not use XMERGE as we need to keep the rights on the folders. So we need the new 2003 to join the 5.5 organisation: we can not create a new organisation and migrate the mailboxes. Is there a way, once the migration is over, to change the organisation name in Exchange 2003 ? Thks, Alex On Sun, 9 Jan 2005 00:11:35 +0100, "Alex" <alex@alex.alex> wrote: >Hi, > >We are migrating our XCH 5.5 to XCH 2003. We need to change the organisation >name. We can not use XMERGE as we n...

error, chkdsk
Questio, when I try to open excel or winmx, an error pops up saying chkdsk, so i ran a chkdsk and it doesnt do anything. The error also says corrupted file. In excel I have some important work that I need to open, and it only opens in Turkish, if I change it to English, its a bunch of boxes and letters, its not my work I need. How do I change it back. What excel version are you using? Try to find somebody with 2003 to see if they can open the file. Otherwise you would look for some commercial software that might be of help http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B30...

count results of formula, not text in formula
I would like to count the results of a formula, format is a mix of numbers and text. A B Formula in Column B is 1 8 (8),(7) ="("&A1&"),("&A3&")" 2 10 (10) =+A2, with custom formatting of (0) to show brackets 3 7 (8),(10) ="("&A1&"),("&A2&")" 4 14 5 9 There are numbers in Column A in rows 1 to 5, there are formulas in Column B. The actual formulas are the column I need to count the requirements. In this example, I need to count ...

4.4.7 error
I have problems on one client that they don't recieve all emails from people sending to them. I guess something is wrong in the configuration. When I do a nslookup -type=mx domain.com I just get unautharized answer. Also the ttl time is just 1800 when I do a dns report. Do the guys that handle the dns change this? The people that doesn't get their mail throw get the 4.4.7 error in return. Best regards Jimmy http://support.microsoft.com/kb/555375 -- Ed Crowley MVP "There are seldom good technological solutions to behavioral problems." .. "Surfer&q...

Outlook 2003 Personal Folders
I just installed Outlook 2003 and I seem to have two Personal Folders in my folders list. But, they both point to the same place and have the same data when I click on them. How do I remove the duplicate? Jeff -- Jeff Grossman (jeff@stikman.com) R click the one that is not your default, then select Close from the context menu. -- Russ Valentine [MVP-Outlook] "Jeff Grossman" <jeff.nospam@stikman.com> wrote in message news:im1jsvsn93v4to7kjlkctmqhjc25om151b@news.stikman.com... > I just installed Outlook 2003 and I seem to have two Personal Folders > in my folders list...

error 0x800CCC0F
Dear Team; I am receiving the following error messages, please assist Could result be sent to lewis.king@riotinto.com regards Lew Your server has unexpectedly terminated the connection. Possible causes for this include server problems, network problems, or a long period of inactivity. Subject 'brocolli & mushroom stir fry noodle', Account: 'pop.gmail.com', Server: 'smtp.gmail.com', Protocol: SMTP, Port: 995, Secure(SSL): Yes, Error Number: 0x800CCC0F Your server has unexpectedly terminated the connection. Possible causes for this include server p...

Excel error codes translations
hi, Does anybody know where I can find a kind of dictionary for Exce errors? The problem is sometimes I get an error message that is not in Englis and without error code and then looking for the solution I must kno its equivalent in English. Regards, Micha -- Message posted from http://www.ExcelForum.com ...

delete server...routing master error
hello, I have 2 exchange servers (exch 2003) and i have to delete one of them. I changed all replication etc...(before we had also a 5.5 which is already removed) but when i stop the services and try to remove the server via system manager of my second exchange (the one that will stay) i get an error message that my server that i want to delete is the routing master for that group. How can i make my other server the routing master ? i don't have any site replication (deleted it because not necessary anymore). thnx, Philippe Go to routing groups > first routing group > mem...

need help coming up with a formula.
I know just enough in Excel that I know I can get the info I want but not enough to know how to do it. Any help would be much appreciated. I am working with 2 columns of data, for example: Days in Stock____________Cost 2_____________________$300 21____________________$400 117___________________$600 8_____________________$400 37____________________$500 78____________________$500 What I want to calculate is the average cost for 0-10 days in stock (($300 + $400)/2= $350), the average cost for 11-60 days in stock, and the average cost for 60+ days in stock. What sort of formula would best cal...

toolbar, date, services, errors, paste, minimized system restore doesn't work
I have a XP system that the toolbar does not show open programs, services do not seem to load at boot, the date is off by one month, I cannot paste anything that I copy, and system restore does not work. All of these issues started at the same time a few days ago. Has anyone else seen this group of errors? I am trying to resolve and do not know if it is a system update gone bad, or if maybe it is a viruse/malware issue. Please let me know if you had had these problems together and what you did to fix them. There is a very good chance that you are seeing the effects of a hi...

formulas #10
Can someone please help me with these 2 formulas that I need to enter into my Spreadsheet?. I am trying to set up a Profit calculator for my Ebay listings. This would be appreciated thanks so much Selling Price - US$25.01 - US$100.00 10.00% of the initial US$25.00 (US$2.50), plus 7.00% of the remaining closing value balance Selling Price - US$100.01 - US$1,000.00 10.00% of the initial US$25.00 (US$2.50), plus 7.00% of the initial US$25.01 - US$100.00 (US$5.25), plus 5.00% of the remaining closing value balance US$100.01 - US$1,000.00 On Feb 7, 11:36 am, "Bacchus" &l...

Re: Money 2004
Thanks for the suggestion. I tried unregistering/re-registering the msxml3 DLL, but still no luck. However, knowing the help file topics are local might help with debugging this problem. thanks, randy "Randy" <nospam@hotmail.com> wrote in message news:... > All, > > I've recently upgraded to Money 2004 Premium from 2003 Deluxe. I'm now > finding that I can't view any help topics--I get the "Unable to load topic. > Click here to try again." I've tried all the suggestions posted here and at > other sites: cleared out temporary in...

set up different default printer for MS Excel
I have a printer A set up as default for my PC. I want to have a printer B to be default one for ALL NEW Excel files. So task is not to change it manually or via some macro button; the task is to have the printer B ready directly after opening of Excel file. Any suggestions? Stefan, You can change the default printer for all applications with the Windows Control Panel. Start - Control Panel - Printers and Faxes. Right-click the printer you wish to be the default, and choose "Set as Default." To set a default printer for Excel only (different from the Windows default printer...