Hi everyone. I have a problem with the trend line of an excel graphic

[Gabi999]

I am trying to fit the following values using a 6th degree polynomial approximation.
The problem is that the trend line gives me the following expression: y = 6E-07x6 - 5E-05x5 + 0.0017x4 - 0.0284x3 + 0.2258x2 - 0.646x + 0.4961. While with the excel on my tablet I get the following: y = 35.384x6 - 147.7x5 + 235.26x4 - 176.72x3 + 60.084x2 - 5.4633x + 0.0491 which is more correct in the range of values I am considering.

My question is, what should I configure so that the second one appears? I have already tried going to Label Options->Number->Category and changing it but this only changes the number of decimals that appear in the output without affecting the calculation.

0	0
0,05	0
0,1	0
0,15	0
0,2	0,15
0,25	0,5
0,3	0,73
0,35	0,78
0,4	0,81
0,45	0,84
0,5	0,86
0,55	0,87
0,6	0,88
0,65	0,89
0,7	0,89
0,75	0,89
0,8	0,9
0,85	0,9
0,9	0,9
0,95	0,9
1	0,9

dfadfa

 

[Kaper]

Probably you selected wrong ranges in PC version, or made some other mistake. (may be you included this dfadfa visible below data or something else).
Because properly set up fit with polynominal finds the same values as you mentioned for tablet. (see firted polynominal equation on screenshot).

But back to the basics - polynominal of 6th order is almost never a good fit line. Note what happened in your case between some 0.02 and 0.12, then badly fitted slope between 0.12 and 0.4 ...

Looking at your data you shall rather fit some kind of sigmoidal line.

I tried to fit one of versions of generalized logistic curve and the quality of fit (while still not perfect) is way better than polynominal. So trying other sigmoidal curves you can probably get almost perfect fit.

An easy way to do it in excel is by using a Solver tool. This tools is installed but not activated in standard instalation, so you shall go to options to activate it. see
Write (in column C) parametrized formula calculating Y on basis of your x (column A) and parameters(i used for them column F)
Finally calculate a sum of squared differences between measured Y and calculated Y (columns B and C) .
hen use Solver to minimize this sum by changing equation parameters.
On first screenshot you can see thick fitted line (the formula for C2 is visible in formula bar), and it is: =$F$1*(1+EXP(-$F$2*(A2-$F$3)))^-$F$5+$F$4
On second one you can see Solver window - the value to be minimized (F6) calculated as =SUMXMY2(B2:B22;C2:C22) and F1:F5 as parametres. I added one constraint that F5 shall be >0

Final notes
- Solver works best if starting point is already not bad . I started from the set:

amplitude	0,9
xscale	20
horizontal	0,25
vertical	0
power	1

and if you enter these values into a sheet you will notice that curve is already better fitting points than polynominal.

- It is sometimes worth rerun Solver without any changes. Sometimes (and I think it is here) it optimizes parameters even better with second (or more) runs. Only when there is no more improvement - you've reached the best possible for Solver value.