Issue #2: Trend Line Equation doesn't Seem to Match Plot

[bhujanga]

My 2nd issue today is that I can’t figure out what is going on with the trend line equations. I have this particular chart to which I have fit a 4th order polynomial trend line. It looks great – just what I would expect. However when I plug the x values into the equation it has provided, I do not get the y values that it is plotting! The whole purpose of this exercise was to come up with this equation so that we can make estimates based on it. Is there something I’m not properly understanding in the way this equation is represented?
For example if I plug in 15 I get:
(.000003 * 15^4) – (.0002 * 15^3) + (.0034 * 15^2) – (.0162 * 15) + .028 =
(.000003 * 50,625) – (.0002 * 3,375) + (.0034 * 225) – (.0162 * 15) + .028 =
0.1519 -0.675 + 0.765 – 0.243 + 0.28 = 0.0269 or 2.69%
But the graph plots it at about 7.1% which is what fits the data.
I've attached a copy of the chart in question.
Here is the data:
3-Day Average
1 0.7%
2 0.5%
3 0.4%
4 0.7%
5 1.0%
6 2.4%
7 3.8%
8 4.4%
9 5.2%
10 6.4%
11 6.9%
12 7.3%
13 6.8%
14 6.8%
15 7.2%
16 6.9%
17 6.8%
18 6.8%
19 5.7%
20 4.5%
21 3.5%
22 2.5%
23 1.8%
24 1.0%

 

[SkipVought]

Hi,

Your picture is pretty, but pretty useless.

Need the chart source data, the chart, the trend...ie the workbook.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Okay, I copied your data. Got the trendline formula from John Walkenbach's site and get close Y data points for each X...

http://spreadsheetpage.com/index.php/tip/chart_trendline_formulas/

[pre]
_c4_ =INDEX(LINEST(y,x^{1,2,3,4}),1)
_c3_ =INDEX(LINEST(y,x^{1,2,3,4}),1,2)
_c2_ =INDEX(LINEST(y,x^{1,2,3,4}),1,3)
_c1_ =INDEX(LINEST(y,x^{1,2,3,4}),1,4)
b =INDEX(LINEST(y,x^{1,2,3,4}),1,5)

y1_ = (_c4_ * x1_^4) + (_c3_ * x1_^3) + (_c2_ * x1_^2) + (_c1_ * x1_^1) + b
x1_ 6 For x1_ of 6 I get a y1_ of 2.45%
[/pre]
...where x and y are Named Ranges for the data in your table.

Unfortunately, you supplied no formulas.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Deniall]

I very vaguely recall this sort of query coming up some time ago on one of these fora, but probably not with the "error" magnitude you seem to be experiencing.[ ] On the previous occasion, if I am remembering correctly, the problem turned out to be that when you get Excel to insert the equation on the graph (like you seem to have done) you lose a lot of precision.[ ] Notice how your coefficients for the quartic and cubic terms have only one significant figure, and that for your squared term has only two.

You should try getting your coefficients directly from the LINEST function (or even calculate them yourself), see whether that solves your problem, and report back.

 

[combo]

As Deniall wrote, you have only one significant digit for two highest powers, this is a source of big uncertainity.
At least in excel 2016 numbers in trend line formula label can be formatted with common format. Try, instead of general, scientific format with 3-4 significant digits and next verify trend line formula results.

combo

 

[SkipVought]

Here's my mock-up upload...

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[bhujanga]

Deniall,
I tried bumping all of the coefficients up or down to the maximum amount the would leave the rounded coefficients unchanged, in the direction that would increase the total. By doing this, I was able to get the total to increase beyond where it needed to be, so I figured your theory about the loss of precision due to rounding of coefficients must be the case. In order to coax out some extra precision, I temporarily increased all of my Y values by a factor of 110,000 and it gave a few extra significant digits with which I was able to get results that looked right! So I made a note of those coefficients and set my data back to how it was.
Thank for the very HELPFUL input.
btw - I investigated the LINEST function, but it only seems to apply to straight line situations.

 

[Deniall]

In the post he lodged one minute before I lodged mine, Skip showed you how to use LINEST function.[ ] It can be used for multiple regression, which in turn can be used for polynomial regression by treating x[sup]2[/sup], x[sup]3[/sup] etc as separate, independent explanatory variables.

 

[SkipVought]

You got 2.69% at x=15
I got 7.44%.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

You can find formulas for all excel chart trendlines at John Walkenbach's The Spreadsheet Page:

http://spreadsheetpage.com/index.php/tip/chart_trendline_formulas/

combo

 

[Deniall]

Bhujanga.[ ] This is nothing to do with your issue as stated, but do you have any a[ ]priori reason for trying to fit a quartic polynomial to your data?[ ] In the absence of some compelling reason I generally steer clear of anything beyond a cubic.[ ] When I look at the .PNG image you provided, the data suggest the possibility of fitting a sinusoidal curve instead of a quartic polynomial.[ ] What do the numbers represent?[ ] Is there any reason that they might possibly be sinusoidal?[ ] Fortnightly icecream sales, for example?

 

[bhujanga]

I don't think they would be sinusoidal. They are traffic counts during 1-hour intervals for just a few days. I have similar sets for different stretches of highway. They are for estimating traffic during a specified hour based on a daily traffic volume, I wanted to find a formula for predicting it specific to a given segment of highway. The only reason I used a quartic was that the lower orders failed to follow the initial down slope at the start of it, and the quartic seemed to fit like a glove. I might have been able to achieve a similarly good fit, had I shifted it, i.e. assigning 5:00 AM to 6:00 AM to hour #1 rather than Midnite to 1:00 AM, but I thought then I'd have had to carry that logic forward and for everything, which seemed like trouble. So, since the quartic fit so well, I went with it. None of the other options provided such a nice match (although, looking at the list of options, I don't have a sinusoidal option - is it something they added after 2010 maybe?)
I'm also not finding the option that combo suggested of reformatting the coefficients to possibly circumvent the accuracy problem. Maybe that's also a later enhancement. Must be time to upgrade!

 

[Deniall]

If your X values are time-of-day, then that alone is a good reason to expect that the data repeats on a 24-hour cycle.[ ] A sine curve would be a starting point for this.[ ] There is no automatic process in Excel to fit a sine curve to data.[ ] You have to do it yourself, but it is not all that hard:[ ] you get Excel's Solver add-in to do the hard work.

I attach an example spreadsheet that does exactly that (using your data).[ ] I initially allowed the process free reign to select the curve's period as well as its amplitude, mean and phase.[ ] But then I changed my mind and enforced a 24-hour period.

The graph on the spreadsheet shows the the curve does not properly allow for the slightly "flatter" data near the peak.[ ] Maybe this problem could be ameliorated by adding a second sine curve, one with a period of 12 hours.[ ] At the very least it would be a good exercise.

 

[combo]

Both excel 2003 and 2016/365: right-click the trendline formula, you should see format data label or format label popup menu item. After clicking it you should be able to format number, set to general. This is the format of formula coefficients.

combo

 

[bhujanga]

Combo - Now I see it. That fixes everything for this situation. I kept clicking on the line itself and trying to find the option in that pop up. I didn't think to right-click on the actual label. Clearly I've not played enough video games.

Daniall, I looked over your Solver method. It's interesting and I'll keep it in mind for the future. I will have quite a lot of these to do and as long as the quartic fits well, I happy with it.

Thanks for all of the help and suggestions!

 

[Deniall]

Bhujanga.[ ] The general point I was trying to make is that if your data is periodic your curve-fitting approximation to that data will be intrinsically better if it too is periodic.[ ] The most natural functions for this are sine functions.[ ] Use a series of sine terms
Y = a[sub]0[/sub] + b[sub]1[/sub]*sin(1*2πX/T+c[sub]1[/sub]) + b[sub]2[/sub]*sin(2*2πX/T+c[sub]2[/sub]) + ··· + b[sub]n[/sub]*sin(n*2πX/T+c[sub]n[/sub])
rather than a series of power terms
Y = a[sub]0[/sub] + a[sub]1[/sub]*X[sup]1[/sup] + a[sub]2[/sub]*X[sup]2[/sup] + ··· + a[sub]n[/sub]*X[sup]n[/sup]
Otherwise you are trying to fit a square numerical peg into a round numerical hole.

You say you will have to do this operation a lot of times.[ ] All the more reason to do it using a sound theoretical approach, because sooner or later you will be hit with some data for which your quartic function approach will not be adequate.

I attach an updated spreadsheet that uses three sine terms.[ ] When updating, I changed the Y values by a factor of 100 to make them consistent with yours (which were percentages rather than pure numbers).[ ] It is worth noting that this scaling made a noticeable difference to the shape of the fitted quartic, which I did not expect.[ ] Another reason to steer clear of polynomials in this sort of context?

It is useful to look at the SSE values for the various approaches:
» Quartic polynomial 0.000287
» One sine term[ ][ ] [ ] [ ] 0.000428
» Two sine terms [ ] [ ] 0.000182
» Three sine terms [ ] 0.000133

 

[combo]

With four parameters I can fit an elephant, and with five I can make him wiggle his trunk.

combo