trend analysis

[fini]

I read somewhere that if you're looking at data over a reasonable period of time, looking to see whether a process is consistent, it's a bad idea to use a trend line in excel. This, I read, was due to it being adversely affected by outliers and its tendency to point to movement in some direction even when there may be none. The website that I read this on suggested a graph based on some formulas. Unfortunately now, when I need to use them, I cannot find the site any more.

Does anyone know anything about this and can suggest an alternative way to monitor change over time than through a trend line?

 

[thepharcyde]

Check out MrExcel.com they are a well clever bunch on there.

 

[manic_man]

"Bad" is a bit of an encompassing term. Certainly outliers can be an issue if you are unaware of them.

Imagine taking the arith mean of these points {1,2,3,300} now it comes out at 77, clearly not a good representation of the set. One way round this would be to use a truncated mean of say 25%, so the top and bottom values are removed, giving us a new mean of 3.

ideally you need to be aware of your outliers and be able to recognise them. All visualization processes are based around the actual data you give them, if you dont want the trendline to reflect your actual data you need to perform some smoothing on the data first.

Excels charting is quite limited, good for what it is, but it was never intended to do much more than visually represent whats already there.

I suggest you use scatter plots to determine if any major anomolies exist in your data and if you wish, remove them, then use a trendline to show the smoothed dataset. Be aware though, stating that a visualization is correlative to a dataset that you have modified but not mentioned the modifications is considered bad form in most circles.

I think perhaps the "equations" you were referring to are the MACD charting style:

http://en.wikipedia.org/wiki/MACD

and perhaps the RSI (relative strength index):

http://en.wikipedia.org/wiki/Relative_strength_index

The RSI style has a comparable alternative in the "Stock" section of Excels charting