(Msg. 1) Posted: Wed Dec 12, 2007 6:36 am
Post subject: Excel 2007 Polynomial Order Incorrect Archived from groups: microsoft>public>excel>charting (more info?)
We recently upgraded to Office 2007. When we attempt to do a trendline in
Excel, the order of polynomials is always one below what it is stated to be
and the highest ordered polynomial is always wrong. (i.e. on a fourth order
polynomial, it will only display polynomials through the third order and in
the equation, the third ordered polynomial value is wrong, the others are
correct).
We tried installing the hot fix from KB 938541 as well as right clicking on
the equation for the chart and adding significant digits under Format
Trendline Label.
A sample file showing the problem can be downloaded from:
(Msg. 2) Posted: Wed Dec 12, 2007 5:31 pm
Post subject: Re: Excel 2007 Polynomial Order Incorrect [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
I was unable to get to your file - my email address was rejected
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email
"Darren" <Darren DeleteThis @discussions.microsoft.com> wrote in message
news:E4215178-AC71-4ADF-93A3-EE5ED07B5BCC@microsoft.com...
> We recently upgraded to Office 2007. When we attempt to do a trendline in
> Excel, the order of polynomials is always one below what it is stated to
> be
> and the highest ordered polynomial is always wrong. (i.e. on a fourth
> order
> polynomial, it will only display polynomials through the third order and
> in
> the equation, the third ordered polynomial value is wrong, the others are
> correct).
>
> We tried installing the hot fix from KB 938541 as well as right clicking
> on
> the equation for the chart and adding significant digits under Format
> Trendline Label.
>
> A sample file showing the problem can be downloaded from:
>
> https://share.chicagogsb.edu/seos/1000/mpd/12012008730e596e383fd90e46f...9af27f9 >
> Thank you in advance for any assistance provided.
>
(Msg. 3) Posted: Wed Dec 12, 2007 5:31 pm
Post subject: Re: Excel 2007 Polynomial Order Incorrect [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
SP1 did not fix the problem.
"Bernard Liengme" wrote:
> Have your downloaded Office 2007 SP1 and tried trendlines again?
> Download info: http://support.microsoft.com/kb/936982 >
> I was unable to get to your file - my email address was rejected
> best wishes
> --
> Bernard V Liengme
> Microsoft Excel MVP
> www.stfx.ca/people/bliengme > remove caps from email
>
> "Darren" <Darren RemoveThis @discussions.microsoft.com> wrote in message
> news:E4215178-AC71-4ADF-93A3-EE5ED07B5BCC@microsoft.com...
> > We recently upgraded to Office 2007. When we attempt to do a trendline in
> > Excel, the order of polynomials is always one below what it is stated to
> > be
> > and the highest ordered polynomial is always wrong. (i.e. on a fourth
> > order
> > polynomial, it will only display polynomials through the third order and
> > in
> > the equation, the third ordered polynomial value is wrong, the others are
> > correct).
> >
> > We tried installing the hot fix from KB 938541 as well as right clicking
> > on
> > the equation for the chart and adding significant digits under Format
> > Trendline Label.
> >
> > A sample file showing the problem can be downloaded from:
> >
> > https://share.chicagogsb.edu/seos/1000/mpd/12012008730e596e383fd90e46f...9af27f9 > >
> > Thank you in advance for any assistance provided.
> >
>
>
>
(Msg. 4) Posted: Thu Dec 13, 2007 12:37 am
Post subject: Re: Excel 2007 Polynomial Order Incorrect [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
On Dec 12, 9:44 pm, Darren <Dar....RemoveThis@discussions.microsoft.com> wrote:
> SP1 did not fix the problem.
If you run the same dataset under 2003 do you get the roughly the same
coefficients for a 4th order fit as are displayed in 2007 but with one
value hidden. Or is 2007 actually doing a 3rd order fit when you ask
for a 4th order?
If you post an ASCII example of test data and observed fitted
equations 2003 vs 2007 for orders 1,2,3,4 then someone might look at
it.
I am a bit surprised at your claim because I have not seen it
misbehave on 1st order linear fits - the display is y = mx + c as
expected. OTOH most of my stuff was created in 2003 and ported to 2007
so any bugs in the Chart Wizard or user interface would not affect me.
The new polynomial fit algorithm used in charts from 2007 is as dumb
as hell and significantly less numerically stable than previously
(borderline at 2nd or 3rd order polynomials depending on the data).
Previously the charts used a clever regularised algorithm that worked
much better than the generic polynomial fit LINEST in the
spreadsheet.
That may have something to do with your problem.
Bin 2007 and ask for your money back. The product is not fit for
purpose. XL2003 charts are way better.
Regards,
Martin Brown
> "Bernard Liengme" wrote:
> > Have your downloaded Office 2007 SP1 and tried trendlines again?
> > Download info:http://support.microsoft.com/kb/936982
>
> > I was unable to get to your file - my email address was rejected
> > best wishes
> > --
> > Bernard V Liengme
> > Microsoft Excel MVP
> >www.stfx.ca/people/bliengme
> > remove caps from email
>
> > "Darren" <Dar....RemoveThis@discussions.microsoft.com> wrote in message
> >news:E4215178-AC71-4ADF-93A3-EE5ED07B5BCC@microsoft.com...
> > > We recently upgraded to Office 2007. When we attempt to do a trendline in
> > > Excel, the order of polynomials is always one below what it is stated to
> > > be
> > > and the highest ordered polynomial is always wrong. (i.e. on a fourth
> > > order
> > > polynomial, it will only display polynomials through the third order and
> > > in
> > > the equation, the third ordered polynomial value is wrong, the others are
> > > correct).
>
> > > We tried installing the hot fix from KB 938541 as well as right clicking
> > > on
> > > the equation for the chart and adding significant digits under Format
> > > Trendline Label.
>
> > > A sample file showing the problem can be downloaded from:
>
> > >https://share.chicagogsb.edu/seos/1000/mpd/12012008730e596e383fd90e46...
>
> > > Thank you in advance for any assistance provided.- Hide quoted text -
>
> - Show quoted text -
(Msg. 5) Posted: Thu Dec 13, 2007 6:10 am
Post subject: Re: Excel 2007 Polynomial Order Incorrect [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
In 2003 we were getting the correct values. 2007 is doing a 3rd order fit
when we click 4th order (and the 3rd order is wrong), a 4th order fit when we
say 5th order, etc.
Darren
"Martin Brown" wrote:
> On Dec 12, 9:44 pm, Darren <Dar... RemoveThis @discussions.microsoft.com> wrote:
> > SP1 did not fix the problem.
>
> If you run the same dataset under 2003 do you get the roughly the same
> coefficients for a 4th order fit as are displayed in 2007 but with one
> value hidden. Or is 2007 actually doing a 3rd order fit when you ask
> for a 4th order?
>
> If you post an ASCII example of test data and observed fitted
> equations 2003 vs 2007 for orders 1,2,3,4 then someone might look at
> it.
>
> I am a bit surprised at your claim because I have not seen it
> misbehave on 1st order linear fits - the display is y = mx + c as
> expected. OTOH most of my stuff was created in 2003 and ported to 2007
> so any bugs in the Chart Wizard or user interface would not affect me.
>
> The new polynomial fit algorithm used in charts from 2007 is as dumb
> as hell and significantly less numerically stable than previously
> (borderline at 2nd or 3rd order polynomials depending on the data).
> Previously the charts used a clever regularised algorithm that worked
> much better than the generic polynomial fit LINEST in the
> spreadsheet.
>
> That may have something to do with your problem.
> Bin 2007 and ask for your money back. The product is not fit for
> purpose. XL2003 charts are way better.
>
> Regards,
> Martin Brown
>
> > "Bernard Liengme" wrote:
> > > Have your downloaded Office 2007 SP1 and tried trendlines again?
> > > Download info:http://support.microsoft.com/kb/936982
> >
> > > I was unable to get to your file - my email address was rejected
> > > best wishes
> > > --
> > > Bernard V Liengme
> > > Microsoft Excel MVP
> > >www.stfx.ca/people/bliengme
> > > remove caps from email
> >
> > > "Darren" <Dar... RemoveThis @discussions.microsoft.com> wrote in message
> > >news:E4215178-AC71-4ADF-93A3-EE5ED07B5BCC@microsoft.com...
> > > > We recently upgraded to Office 2007. When we attempt to do a trendline in
> > > > Excel, the order of polynomials is always one below what it is stated to
> > > > be
> > > > and the highest ordered polynomial is always wrong. (i.e. on a fourth
> > > > order
> > > > polynomial, it will only display polynomials through the third order and
> > > > in
> > > > the equation, the third ordered polynomial value is wrong, the others are
> > > > correct).
> >
> > > > We tried installing the hot fix from KB 938541 as well as right clicking
> > > > on
> > > > the equation for the chart and adding significant digits under Format
> > > > Trendline Label.
> >
> > > > A sample file showing the problem can be downloaded from:
> >
> > > >https://share.chicagogsb.edu/seos/1000/mpd/12012008730e596e383fd90e46...
> >
> > > > Thank you in advance for any assistance provided.- Hide quoted text -
> >
> > - Show quoted text -
>
>
(Msg. 6) Posted: Thu Oct 22, 2009 10:00 am
Post subject: Also problems with polynomial [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
I also have trouble with the formula from adding a trendline to a 4th order polynomial. I graphed data in both 2003 and 2007 and added a 4th order trendline and displayed the formula on the graph. In both versions, I got the same formula, specifically, y = 0.00000095x4 - 0.00753977x3 + 22.51174905x2 - 29,872.75x + 14,865,177. This yielded 46690 for my text x value of 1980, but I expected a number less than 1.
The formula using linest was: y = -3.3105E-10x^4+1.77394E-06x^3+-0.002673x^2+0x+1798.06, which yields the expected 0.0065 for the x value of 1980.
Quite a bug!
Note: I also tried a 2nd order polynomial. The results were the same using linest and using the formula on the chart from the treadline. No bugs.
Martin Brown wrote:
Re: Excel 2007 Polynomial Order Incorrect
14-Dec-07
If you run the same dataset under 2003 do you get the roughly the same
coefficients for a 4th order fit as are displayed in 2007 but with one
value hidden. Or is 2007 actually doing a 3rd order fit wh
Previous Posts In This Thread:
On Wednesday, December 12, 2007 9:36 AM
Darre wrote:
Excel 2007 Polynomial Order Incorrect
We recently upgraded to Office 2007. When we attempt to do a trendline in
Excel, the order of polynomials is always one below what it is stated to be
and the highest ordered polynomial is always wrong
On Wednesday, December 12, 2007 4:31 PM
Bernard Liengme wrote:
Have your downloaded Office 2007 SP1 and tried trendlines again?
Have your downloaded Office 2007 SP1 and tried trendlines again?
Download info: http://support.microsoft.com/kb/936982
I was unable to get to your file - my email address was rejected
best wishes
--
On Wednesday, December 12, 2007 4:44 PM
Darre wrote:
Re: Excel 2007 Polynomial Order Incorrect
SP1 did not fix the problem.
"Bernard Liengme" wrote:
On Thursday, December 13, 2007 9:10 AM
Darre wrote:
In 2003 we were getting the correct values. 2007 is doing a 3rd order fit
when we click 4th order (and the 3rd order is wron
On Friday, December 14, 2007 2:58 AM
Martin Brown wrote:
Re: Excel 2007 Polynomial Order Incorrect
If you run the same dataset under 2003 do you get the roughly the same
coefficients for a 4th order fit as are displayed in 2007 but with one
value hidden. Or is 2007 actually doing a 3rd order fit wh
(Msg. 7) Posted: Thu Oct 22, 2009 2:12 pm
Post subject: Re: Also problems with polynomial [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
I'll leave out any comments about the suitability of a 4th order trendline.
Two things come to mind.
First, you should format the trendline equation using a scientific
number format with lots of digits.
Second, are you using an XY Scatter chart, or a line chart? The two
differ not in formatting of the plotted data but in the treatment of the
X variable.
Heatjer Mayes wrote:
> I also have trouble with the formula from adding a trendline to a 4th
> order polynomial. I graphed data in both 2003 and 2007 and added a
> 4th order trendline and displayed the formula on the graph. In both
> versions, I got the same formula, specifically, y = 0.00000095x4 -
> 0.00753977x3 + 22.51174905x2 - 29,872.75x + 14,865,177. This yielded
> 46690 for my text x value of 1980, but I expected a number less than
> 1.
>
> The formula using linest was: y =
> -3.3105E-10x^4+1.77394E-06x^3+-0.002673x^2+0x+1798.06, which yields
> the expected 0.0065 for the x value of 1980.
>
> Quite a bug!
>
> Note: I also tried a 2nd order polynomial. The results were the same
> using linest and using the formula on the chart from the treadline.
> No bugs.
>
(Msg. 8) Posted: Fri Oct 23, 2009 12:05 pm
Post subject: Re: Also problems with polynomial [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
"Heatjer Mayes" wrote in message
news:2009102213053heather.mayes@jacobs.com...
> I also have trouble with the formula from adding a trendline to a 4th
> order polynomial. I graphed data in both 2003 and 2007 and added a 4th
> order trendline and displayed the formula on the graph. In both versions,
> I got the same formula, specifically, y = 0.00000095x4 - 0.00753977x3 +
> 22.51174905x2 - 29,872.75x + 14,865,177. This yielded 46690 for my text x
> value of 1980, but I expected a number less than 1.
>
> The formula using linest was: y
> = -3.3105E-10x^4+1.77394E-06x^3+-0.002673x^2+0x+1798.06, which yields the
> expected 0.0065 for the x value of 1980.
>
> Quite a bug!
>
> Note: I also tried a 2nd order polynomial. The results were the same
> using linest and using the formula on the chart from the treadline. No
> bugs.
>
>
>
>
>
> Martin Brown wrote:
>
> Re: Excel 2007 Polynomial Order Incorrect
> 14-Dec-07
>
> If you run the same dataset under 2003 do you get the roughly the same
> coefficients for a 4th order fit as are displayed in 2007 but with one
> value hidden. Or is 2007 actually doing a 3rd order fit wh
>
> Previous Posts In This Thread:
>
> On Wednesday, December 12, 2007 9:36 AM
> Darre wrote:
>
> Excel 2007 Polynomial Order Incorrect
> We recently upgraded to Office 2007. When we attempt to do a trendline in
> Excel, the order of polynomials is always one below what it is stated to
> be
> and the highest ordered polynomial is always wrong
>
> On Wednesday, December 12, 2007 4:31 PM
> Bernard Liengme wrote:
>
> Have your downloaded Office 2007 SP1 and tried trendlines again?
> Have your downloaded Office 2007 SP1 and tried trendlines again?
> Download info: http://support.microsoft.com/kb/936982 >
> I was unable to get to your file - my email address was rejected
> best wishes
> --
>
> On Wednesday, December 12, 2007 4:44 PM
> Darre wrote:
>
> Re: Excel 2007 Polynomial Order Incorrect
> SP1 did not fix the problem.
>
> "Bernard Liengme" wrote:
>
> On Thursday, December 13, 2007 9:10 AM
> Darre wrote:
>
> Re: Excel 2007 Polynomial Order Incorrect
> Martin,
>
> Here is a link to the file: http://www.mediafire.com/?7g4abmw30wn >
> In 2003 we were getting the correct values. 2007 is doing a 3rd order fit
> when we click 4th order (and the 3rd order is wron
>
> On Friday, December 14, 2007 2:58 AM
> Martin Brown wrote:
>
> Re: Excel 2007 Polynomial Order Incorrect
> If you run the same dataset under 2003 do you get the roughly the same
> coefficients for a 4th order fit as are displayed in 2007 but with one
> value hidden. Or is 2007 actually doing a 3rd order fit wh
>
> EggHeadCafe - Software Developer Portal of Choice
> ASP.NET Color Tool Derived from Reflection
> http://www.eggheadcafe.com/tutorials/aspnet/a657aa86-1abe-4a5f-9c04-bb...fb8bfb7
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office