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

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

(OP)

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%

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%

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

Your picture is pretty, but pretty useless.

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

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Issue #2: Trend Line Equation doesn't Seem to Match Plot

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

...where x and y are Named Ranges for the data in your table.

Unfortunately, you supplied no formulas.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Issue #2: Trend Line Equation doesn't Seem to Match Plot

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.

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

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

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

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Issue #2: Trend Line Equation doesn't Seem to Match Plot

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.

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

multipleregression, which in turn can be used forpolynomialregression by treating x^{2}, x^{3}etc as separate, independent explanatory variables.## RE: Issue #2: Trend Line Equation doesn't Seem to Match Plot

I got 7.44%.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Issue #2: Trend Line Equation doesn't Seem to Match Plot

combo

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

a priorireason 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?## RE: Issue #2: Trend Line Equation doesn't Seem to Match Plot

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!

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

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.

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

combo

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

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!

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

Y = a

_{0}+ b_{1}*sin(1*2πX/T+c_{1}) + b_{2}*sin(2*2πX/T+c_{2}) + ··· + b_{n}*sin(n*2πX/T+c_{n})rather than a series of power terms

Y = a

_{0}+ a_{1}*X^{1}+ a_{2}*X^{2}+ ··· + a_{n}*X^{n}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

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

combo