Polynominal equation error

[jayjones90]

Hi, Im using a polynominal equation for a last year thesis but have been having a problem.

I have used every polynominal order however none are giving me the correct answer which im using to compare with measured data in order to validate the polynominal equation is working correctly, it is not as you can see in my attached image i have used 10 as "x" however answer is not giving me the same answer as he measured data for 10.

how could i fix this issue? decimal places have already been increased but made no difference.

I have already imported the data on Matlab and used the polynominal function on there instead which did give an answer identical to the measured data so im just curious into what the issue with excel is?

Thanks

 

[joeu2004]

Re: help with trendline

Upload an example Excel file (redacted) that demonstrates the problem to file-sharing website and post the public/share URL in a response here.

If your chart image shows the trendline, there is something very wrong with the facts as you present them.

The chart seems to show that the trendline passes through about 0.6 for x=10. But your Excel formula returns about 0.4, a difference that we could see in the chart if that were the trendline value.

Off-hand, I do not see an error in the Excel formula. But it is easy to misread such numbers.

 

[joeu2004]

Re: help with trendline

Oh, I suspect you used a line chart. You must use an xy scatter chart.

I'm on a smartphone and typing is difficult. I'll explain latef.

 

[joeu2004]

Re: help with trendline

As I mentioned, I suspect that you created a line chart instead of an XY scatter chart.

As explained in KB 211967 (click here) [1] (but only the "Note" is correct!), trendline does not work as expected in line charts. In line charts, trendline always uses x=1,2,... instead of the values in the x-axis, which are just labels.

[1] https://support.microsoft.com/en-us/kb/211967

Consequently, we must use an XY scatter chart in order to get accurate trendline coefficients (unless x truly is 1,2,...).

This is demonstrated below.

The line chart trendline coefficients are G4:G10.

The XY scatter chart trendline are in H4:H10.

The values in D4:D26 are based on B4:B26 for x and G4:G10 for the coefficients. Note the similarity to the data that in the image that you posted.

The values in E4:E26 are based on C4:C26 for x and H4:H10 for the coefficients. Note the similarity to the data in B4:B26.

-----

The methods that I used might be instructive.

Notice that the trendline label in the XY scatter chart is formatted Scientific with 14 decimal places. Although that might be difficult to read, it ensures that all 15 significant digits are formatted, regardless of the magnitude of the number.

Similarly, H4:H10 is formatted as Scientific with 14 decimal places.

The coefficients in G4:G10 were entered manually, based on the image in your posting.

The coefficients in H4:H10 could have been entered manually as well, based on the trendline label in the XY scatter chart.

Instead, I selected H4:H10 and array-entered the following formula (press ctrl+shift+Enter instead of just Enter):

=TRANSPOSE(LINEST(D4:D26,C4:C26^{1,2,3,4,5,6}))

TRANSPOSE is used because I chose a vertical range of cells in order to better fit this posting. Normally, I choose a horizontal range; in that case, TRANSPOSE would not and should not be used.

Note the LINEST coefficients in H4:H10 do not exactly match the trendline coefficients in the XY scatter chart. There are many reasons for that; usually, it does not matter. However, if the LINEST coefficients did not work, we should copy the trendline coefficients from the chart.

The values in D4:D26 are intended to represent your original data. Since I did not have that data, I estimated it based on the line chart coefficients in G4:G10 by entering the following formula into D4 and copying down through D26:

=SERIESSUM(B4, 6, -1, $G$4:$G$10)

Similarly, the values in E4:E26 are intended to represent the estimated-y data that you would get based on the XY scatter chart or LINEST coefficients in H4:H10. I entered the following formula in E4 and copied down through E26:

=SERIESSUM(C4, 6, -1, $H$4:$H$9) + $H$10

Note the difference: the x^0 coefficient (constant) cannot be included in the last SERIESSUM parameter because x=0 is included in the actual x-axis values in C4:C26. Because of that limitation, the formula in E4 is the more reliable way to use SERIESSUM, IMHO.

 

[arthurbr]

Problem was solved on cross post at EF Polynomial equation error

 

[RoryA]

Also cross-posted here: trendline error

@jayjones90: Please take a minute to read the forum rules, especially as they relate to cross-posting, and try to abide by them in future. Thanks.

PS I've also merged your duplicate thread into this one.