|
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. |
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. |
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 |
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? |
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 |
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? |
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 |
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! |
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. |
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? |
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? |
| All times are GMT +1. The time now is 09:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com