Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10
Default Trendline formula not correct

Hi everybody,

I use scatter charts and trendlines a lot to derive formulas from real data.. Excel then prints the formula it used on the chart. However, when I use this formula with the same data set the values always differ and I have to massage the formula to make it represent the data. What am I doing wrong here?
Thanks for your help.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11
Default Trendline formula not correct

On Fri, 20 Jan 2017 10:22:01 -0800 (PST), BottleMan wrote:

Hi everybody,

I use scatter charts and trendlines a lot to derive formulas from real data. Excel then prints the formula it used on the chart. However, when I use this formula with the same data set the values always differ and I have to massage the formula to make it represent the data. What am I doing wrong here?
Thanks for your help.


What do you mean by, "when I use this formula?"

Are you using the full precision of the constants produced by Excel?

I have found with nonlinear regression that rounding the constants can
produce results surprisingly far from the expected values.


  #3   Report Post  
Banned
 
Posts: 6
Default

up phụ shop nÃ*o nhá»› thanks em nha em lÃ* khách quen của shop đó hi hi ^^
_________________
curso micropigmentação capilar

https://t.co/DSMM4fAo7a
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10
Default Trendline formula not correct

On Monday, January 23, 2017 at 2:53:04 AM UTC-5, Wally W. wrote:
On Fri, 20 Jan 2017 10:22:01 -0800 (PST), BottleMan wrote:

Hi everybody,

I use scatter charts and trendlines a lot to derive formulas from real data. Excel then prints the formula it used on the chart. However, when I use this formula with the same data set the values always differ and I have to massage the formula to make it represent the data. What am I doing wrong here?
Thanks for your help.


What do you mean by, "when I use this formula?"

Are you using the full precision of the constants produced by Excel?

I have found with nonlinear regression that rounding the constants can
produce results surprisingly far from the expected values.


I use the exact formula that Excel displays. Here is a typical data set:
x-Axis Y-axis
1.2 0.5
1.6 1
2.4 1.5
3.6 2
4.8 1.5
6 1
7.2 0.6
8 0.3
Here is the formula Excel displays:
y = 0.0046x4 - 0.0455x3 - 0.0981x2 + 1.5795x - 1.1698 with R² = 0.9843
Here are the values I get when I use the formula on the same x-values to calculate the Y-values:
x-Axis Y-axis
1.2 0.54
1.6 1.07
2.4 2.20
3.6 3.56
4.8 5.08
6 7.31
7.2 6.98
8 -18.11
That is even though the curve that Excel draws hugs the values very closely.. So what is going on?



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default Trendline formula not correct

Hi,

Am Wed, 25 Jan 2017 10:34:47 -0800 (PST) schrieb BottleMan:

I use the exact formula that Excel displays. Here is a typical data set:
x-Axis Y-axis
1.2 0.5
1.6 1
2.4 1.5
3.6 2
4.8 1.5
6 1
7.2 0.6
8 0.3
Here is the formula Excel displays:
y = 0.0046x4 - 0.0455x3 - 0.0981x2 + 1.5795x - 1.1698 with R² = 0.9843
Here are the values I get when I use the formula on the same x-values to calculate the Y-values:
x-Axis Y-axis
1.2 0.54
1.6 1.07
2.4 2.20
3.6 3.56
4.8 5.08
6 7.31
7.2 6.98
8 -18.11
That is even though the curve that Excel draws hugs the values very closely. So what is going on?


try it with a higher order for the trendline formula and play around
with "Set Intercept":
With "Set Intercept" to 1 and the formula
y = 0,0002x^6 - 0,0084x^5 + 0,123x^4 - 0,8225x^3 + 2,4397x^2 - 2,3116x + 1
the result is a little bit better.


Regards
Claus B.
--
Windows10
Office 2016


  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10
Default Trendline formula not correct

On Friday, January 20, 2017 at 1:22:05 PM UTC-5, BottleMan wrote:
Hi everybody,

I use scatter charts and trendlines a lot to derive formulas from real data. Excel then prints the formula it used on the chart. However, when I use this formula with the same data set the values always differ and I have to massage the formula to make it represent the data. What am I doing wrong here?
Thanks for your help.




On Friday, January 20, 2017 at 1:22:05 PM UTC-5, BottleMan wrote:
Hi everybody,

I use scatter charts and trendlines a lot to derive formulas from real data. Excel then prints the formula it used on the chart. However, when I use this formula with the same data set the values always differ and I have to massage the formula to make it represent the data. What am I doing wrong here?
Thanks for your help.


With the formula you provided the y-values are like this:
x-Axis Y-axis
1.2 -1.175283891
1.6 -3.196520141
2.4 -12.23920302
3.6 -44.71833352
4.8 -109.1028104
6 -215.2796
7.2 -373.5357904
8 -513.4864
Much worse. Can you show me what you got?
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default Trendline formula not correct

Hi,

Am Wed, 25 Jan 2017 13:58:04 -0800 (PST) schrieb BottleMan:

With the formula you provided the y-values are like this:
x-Axis Y-axis
1.2 -1.175283891
1.6 -3.196520141
2.4 -12.23920302
3.6 -44.71833352
4.8 -109.1028104
6 -215.2796
7.2 -373.5357904
8 -513.4864
Much worse. Can you show me what you got?


when you change the values you have to play around with the trendline
properties to smooth the trendline to you data.
With the values above it is enough to have a formula with 3ed order:
y = -0,9906x3 - 0,3113x2 + 1,8738x - 1,2828



Regards
Claus B.
--
Windows10
Office 2016
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10
Default Trendline formula not correct

On Friday, January 20, 2017 at 1:22:05 PM UTC-5, BottleMan wrote:
Hi everybody,

I use scatter charts and trendlines a lot to derive formulas from real data. Excel then prints the formula it used on the chart. However, when I use this formula with the same data set the values always differ and I have to massage the formula to make it represent the data. What am I doing wrong here?
Thanks for your help.


Well, playing around with the data is what I have been doing but I don't understand why Excel would give me a formula that does not work even though the trendline it draws hugs the values very well!
  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11
Default Trendline formula not correct

On Wed, 25 Jan 2017 10:34:47 -0800 (PST), BottleMan wrote:

On Monday, January 23, 2017 at 2:53:04 AM UTC-5, Wally W. wrote:
On Fri, 20 Jan 2017 10:22:01 -0800 (PST), BottleMan wrote:

Hi everybody,

I use scatter charts and trendlines a lot to derive formulas from real data. Excel then prints the formula it used on the chart. However, when I use this formula with the same data set the values always differ and I have to massage the formula to make it represent the data. What am I doing wrong here?
Thanks for your help.


What do you mean by, "when I use this formula?"

Are you using the full precision of the constants produced by Excel?

I have found with nonlinear regression that rounding the constants can
produce results surprisingly far from the expected values.


I use the exact formula that Excel displays. Here is a typical data set:
x-Axis Y-axis
1.2 0.5
1.6 1
2.4 1.5
3.6 2
4.8 1.5
6 1
7.2 0.6
8 0.3
Here is the formula Excel displays:
y = 0.0046x4 - 0.0455x3 - 0.0981x2 + 1.5795x - 1.1698 with R² = 0.9843
Here are the values I get when I use the formula on the same x-values to calculate the Y-values:
x-Axis Y-axis
1.2 0.54
1.6 1.07
2.4 2.20
3.6 3.56
4.8 5.08
6 7.31
7.2 6.98
8 -18.11
That is even though the curve that Excel draws hugs the values very closely. So what is going on?


First, using your data: Excel doesn't show me the formula you posted.

I see:
y = -0.0006x4 + 0.038x3 - 0.5525x2 + 2.5491x - 1.8375
R² = 0.9816


Then: Excel shows the constants at reduced precision.

Using the poorly documented, unintuitive, and cumbersome 'linest'
function, these are the coefficients to more decimal places for your
data:

-0.000567859
0.038003496
-0.552495008
2.549077276
-1.837537486


=LINEST(F5:F12,E5:E12^{1,2,3,4},TRUE,TRUE)

Your y-values are in column F, your x values are in column E.

You weren't expecting the x-values to appear first in the formula,
were you? Why should M$ bother with usual conventions?

The quite user-unfriendly procedu
1. Enter that formula above (with the cryptic call for a 4th order
polynomial)
2. Highlight the cells where you want the coefficients to appear
I highlighted cells in a row. As I recall, that is required. Highlight
enough cells to receive all your coefficients.
3. Then the best part (a brain fart from some coder after too much
coffee):
3.a: Press F2 to edit the cell where the formula is.
3.b: Press ctrl-shift-enter (after saying "Mother may I' forwards
and backwards three times)

Then ... it is an array. So if you want to change something, you
can't. You need to redo it in some cells that aren't an array already.



  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11
Default Trendline formula not correct

On Sat, 28 Jan 2017 14:35:16 -0500, Wally W. wrote:

On Wed, 25 Jan 2017 10:34:47 -0800 (PST), BottleMan wrote:

On Monday, January 23, 2017 at 2:53:04 AM UTC-5, Wally W. wrote:
On Fri, 20 Jan 2017 10:22:01 -0800 (PST), BottleMan wrote:

Hi everybody,

I use scatter charts and trendlines a lot to derive formulas from real data. Excel then prints the formula it used on the chart. However, when I use this formula with the same data set the values always differ and I have to massage the formula to make it represent the data. What am I doing wrong here?
Thanks for your help.

What do you mean by, "when I use this formula?"

Are you using the full precision of the constants produced by Excel?

I have found with nonlinear regression that rounding the constants can
produce results surprisingly far from the expected values.


I use the exact formula that Excel displays. Here is a typical data set:
x-Axis Y-axis
1.2 0.5
1.6 1
2.4 1.5
3.6 2
4.8 1.5
6 1
7.2 0.6
8 0.3
Here is the formula Excel displays:
y = 0.0046x4 - 0.0455x3 - 0.0981x2 + 1.5795x - 1.1698 with R² = 0.9843
Here are the values I get when I use the formula on the same x-values to calculate the Y-values:
x-Axis Y-axis
1.2 0.54
1.6 1.07
2.4 2.20
3.6 3.56
4.8 5.08
6 7.31
7.2 6.98
8 -18.11
That is even though the curve that Excel draws hugs the values very closely. So what is going on?


First, using your data: Excel doesn't show me the formula you posted.

I see:
y = -0.0006x4 + 0.038x3 - 0.5525x2 + 2.5491x - 1.8375
R² = 0.9816


Then: Excel shows the constants at reduced precision.

Using the poorly documented, unintuitive, and cumbersome 'linest'
function, these are the coefficients to more decimal places for your
data:

-0.000567859
0.038003496
-0.552495008
2.549077276
-1.837537486


=LINEST(F5:F12,E5:E12^{1,2,3,4},TRUE,TRUE)

Your y-values are in column F, your x values are in column E.

You weren't expecting the x-values to appear first in the formula,
were you? Why should M$ bother with usual conventions?

The quite user-unfriendly procedu
1. Enter that formula above (with the cryptic call for a 4th order
polynomial)
2. Highlight the cells where you want the coefficients to appear
I highlighted cells in a row. As I recall, that is required. Highlight
enough cells to receive all your coefficients.
3. Then the best part (a brain fart from some coder after too much
coffee):
3.a: Press F2 to edit the cell where the formula is.


Note: Press F2 while all the target cells are highlighted.

3.b: Press ctrl-shift-enter (after saying "Mother may I' forwards
and backwards three times)

Then ... it is an array. So if you want to change something, you
can't. You need to redo it in some cells that aren't an array already.


The coefficients will be in the highlighted cells -- which are now an
array (whatever that means).

Next on the list of crazy Excel features: The contents of a cell can
be hidden by using a custom format that consists of three semicolons.

http://www.howtogeek.com/218582/how-...umns-in-excel/

Who makes this **** up?!

Does anyone have a clue how easy it was to hide a cell in Quattro?



  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11
Default Trendline formula not correct

On Sat, 28 Jan 2017 14:49:49 -0500, Wally W. wrote:

On Sat, 28 Jan 2017 14:35:16 -0500, Wally W. wrote:

On Wed, 25 Jan 2017 10:34:47 -0800 (PST), BottleMan wrote:

On Monday, January 23, 2017 at 2:53:04 AM UTC-5, Wally W. wrote:
On Fri, 20 Jan 2017 10:22:01 -0800 (PST), BottleMan wrote:

Hi everybody,

I use scatter charts and trendlines a lot to derive formulas from real data. Excel then prints the formula it used on the chart. However, when I use this formula with the same data set the values always differ and I have to massage the formula to make it represent the data. What am I doing wrong here?
Thanks for your help.

What do you mean by, "when I use this formula?"

Are you using the full precision of the constants produced by Excel?

I have found with nonlinear regression that rounding the constants can
produce results surprisingly far from the expected values.

I use the exact formula that Excel displays. Here is a typical data set:
x-Axis Y-axis
1.2 0.5
1.6 1
2.4 1.5
3.6 2
4.8 1.5
6 1
7.2 0.6
8 0.3
Here is the formula Excel displays:
y = 0.0046x4 - 0.0455x3 - 0.0981x2 + 1.5795x - 1.1698 with R² = 0.9843
Here are the values I get when I use the formula on the same x-values to calculate the Y-values:
x-Axis Y-axis
1.2 0.54
1.6 1.07
2.4 2.20
3.6 3.56
4.8 5.08
6 7.31
7.2 6.98
8 -18.11
That is even though the curve that Excel draws hugs the values very closely. So what is going on?


First, using your data: Excel doesn't show me the formula you posted.

I see:
y = -0.0006x4 + 0.038x3 - 0.5525x2 + 2.5491x - 1.8375
R² = 0.9816


Then: Excel shows the constants at reduced precision.

Using the poorly documented, unintuitive, and cumbersome 'linest'
function, these are the coefficients to more decimal places for your
data:

-0.000567859
0.038003496
-0.552495008
2.549077276
-1.837537486


=LINEST(F5:F12,E5:E12^{1,2,3,4},TRUE,TRUE)

Your y-values are in column F, your x values are in column E.

You weren't expecting the x-values to appear first in the formula,
were you? Why should M$ bother with usual conventions?

The quite user-unfriendly procedu
1. Enter that formula above (with the cryptic call for a 4th order
polynomial)
2. Highlight the cells where you want the coefficients to appear
I highlighted cells in a row. As I recall, that is required. Highlight
enough cells to receive all your coefficients.
3. Then the best part (a brain fart from some coder after too much
coffee):
3.a: Press F2 to edit the cell where the formula is.


Note: Press F2 while all the target cells are highlighted.


Of course, it is intuitive that the target cells to receive the
coefficients include the cell where the 'linest' formula is.

That *was* intiutive, wasn't it?

It was at least as intutive as using a function named 'linest' for
nonlinear regression, wasn't it?

3.b: Press ctrl-shift-enter (after saying "Mother may I' forwards
and backwards three times)

Then ... it is an array. So if you want to change something, you
can't. You need to redo it in some cells that aren't an array already.


The coefficients will be in the highlighted cells -- which are now an
array (whatever that means).

Next on the list of crazy Excel features: The contents of a cell can
be hidden by using a custom format that consists of three semicolons.

http://www.howtogeek.com/218582/how-...umns-in-excel/

Who makes this **** up?!

Does anyone have a clue how easy it was to hide a cell in Quattro?


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Trendline formula does not mimic actual trendline BottleMan Excel Discussion (Misc queries) 11 September 24th 15 10:51 PM
Trendline formula juanpablo Excel Discussion (Misc queries) 6 October 18th 09 08:56 AM
Formula in Trendline juanpablo Excel Discussion (Misc queries) 0 October 16th 09 08:20 PM
log trendline formula dr chuck Excel Programming 5 February 17th 07 09:14 PM
Using Trendline Formula Maarten Excel Programming 2 February 12th 05 03:14 AM


All times are GMT +1. The time now is 04:40 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"