#STATISTICS EXCEL TRENDLINE SERIES#

Whenever you collect data in Microsoft Excel or another program, you should always examine it for trends. Here is a link to 2 Excel sheets on my public OneDrive / SkyDrive (. Why this is so and how can I change that - I don't know. Sure I have seen the buttons - I am not able to insert links / pictures, because my 'Account is not yet verified'.

I rechecked with Mathematica: my algorithm worked And, heureka, the result was different from that of Excel. In my program, out of suspicion, I did not use logarithms for my expo trend, but a direct least square method (algorithm of Levenberg-Marquardt). For deciding which is the best trend, I wanted to have an overview of all of Excel's trendlines in one sheet - so I had to rewrite the logic behind Excel I detected all this, when I wrote a program to analyze huge amounts of experimental data repeatedly. In this way, a 'deformation' of a to-be-analyzed situation can occur. Logarithm dramatically shrinks the size of a value - the more, the larger the value is. The reason for this phenomenon lies in logarithm's nature: ).ДЄt least it is interesting to realize, that an optimization for the (x, ln(y)) pairs is not in general optimal for the original data. The more basic issue 'just how optimal is my expo trendline?' could of course be argued against (not an exact science. The same thing happens when you try a power trendline. The only problem Excel has: calculating RВІ 'too early'. Like most people would, Excel works on the assumption, that exponentially distributed data should yield a straight line, when their logarithms are used instead. This value, while technically correctly calculated, is just the wrong one for my original data pairs (x,y). Your can easily check this out directly and without any statistics expertise. The RВІ displayed by Excel in the diagram is that for the data pairs (x,ln(y)) approximated by a straight line. One more thing, curiosity how did you derive your more optimal formula 10.994*Exp(0,0182311x) ? To help someone close to me through a stats exam!) Until I look into it further I'll hold back from saying it really is wrong for two reasons if wrong it would be surprising if this hasn't been reported before and fixed, also I am not knowledgeable enough about statistics (a problem as I am urgently trying OK, I understand why you think it's wrong! So the formula the trendline formula seems to be returningВ RВІ with one method with an exponential formula and a different method with a polynomial method.

#STATISTICS EXCEL TRENDLINE SERIES#

Similarly I calculated a series of Ys with this formula, however this time comparing Ys I return the same value for RВІ = 0.952370227 I added a 2-order polynomial and the trendline formula returned I calculated a series of Ys with 22.9134805762*Exp(0.0168832568x), and comparing these Ys with the original Ys I also get your value RВІ=0.9394636 using =RSQ() With your data I replicated your RВІ=0.9302848013 in the formula shown on the chart.

Unfortunately you can't include file attachments but you can of course upload a file to any file sharing site, eg OneDrive (make sure it's shared) ДЇor your earlier question, you can include links and images using the icons above the frame where you post. A hypothesis you would have clearly dismissed before. With this information you could almost believe that your data indeed follows an exponential pattern. You will get a better approximation, and an even better RВІ (0.941603). Second, the above formula is not optimal! Try instead:10.994*Exp(0,0182311x). Insert a diagram and an exponential trendline with formula and RВІ for these data. If the exponential trendline however is systematically wrong or unreliable, that feature of Excel looses value. If you want to know whether your data follows an exponential pattern a*Exp(b*x), then Excel should be a help. The diagramm displays an exponential equation all right, but an RВІ from a different equation (the one from the linear trendline to (x,ln) data)!

If you take Wing's sheet and insert a column with a*Exp(b*x) values (a and b from the formula in the diagram) and then calculate the RВІ (with the worksheet function!) between this column and the Y column, you will arrive at RВІ=0.989. If you, however, cannot rely on what you see in an exponential trendline diagram. RВІ in this context is an important indicator: if under 0,95 you should forget the corresponding trendline (for the moment at least). Probably follow an exponential or other pattern.

ДЇirst of all: I agree with you - trendlines are not an exact science, but a help. I can send lots of stuff by e-mail of course. As a new user in TechNet, I am still suffering from some drawbacks - like the one that I cannot post links nor images.

Statistics excel trendline