Home |
Search |
Today's Posts |
#1
|
|||
|
|||
When and Why Trendlines may not be calculated correctly?
I have been trying to analyse some non financial data with Excel.
I did pay to do an Excel course of several weeks, but they only touched upon accounting uses but nothing on statistics. I am very much a novice with Excel. I have beening doing line graphs combined with trendlines. I recently read that trendlines are not accurate unless scatterplots are used. So can someone give me a rough guesstimate what the range of that inaccuracy might be? And a very brief idea of what might cause that inaccuracy? TIA, Nev. |
#2
|
|||
|
|||
Nev -
A scatter chart uses real data values for the X coordinate. The error that results from using categories (which Excel treats as 1, 2, 3, etc.) depends on how different the real data is from a simple set of counting numbers. One major cause of inaccuracy is simply in not showing sufficient digits in the trendline formula. When the formula is showing, you should double click on it, select the Number tab, and choose a Scientific format with lots of digits showing. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Nev. wrote: I have been trying to analyse some non financial data with Excel. I did pay to do an Excel course of several weeks, but they only touched upon accounting uses but nothing on statistics. I am very much a novice with Excel. I have beening doing line graphs combined with trendlines. I recently read that trendlines are not accurate unless scatterplots are used. So can someone give me a rough guesstimate what the range of that inaccuracy might be? And a very brief idea of what might cause that inaccuracy? TIA, Nev. |
#3
|
|||
|
|||
Thanks Jon.
I was analysing children's sport and one of my graphs came up with a negative trendline for children's results for the season. So now I guess I have to relearn to do my graphs all over again with scatterplots. Sigh. I just thought I had proved everybody else wrong. I wasn't using formulas, just data tabulated from children's results. Nev. "Jon Peltier" wrote... A scatter chart uses real data values for the X coordinate. The error depends on how different the real data is from a simple set of counting numbers. When the formula is showing, you should double click on it, select the Number tab, and choose a Scientific format with lots of digits showing. |
#4
|
|||
|
|||
No, there is no rule that a trendline is automatically inaccurate for
anything other than a XY Scatter chart. It all depends on what you have on the x-axis. Suppose you are plotting week number (1, 2, 3, etc.) on the x-axis and some kind of a score on the y-axis *and* you have data for each week between the start and the end of the season. Then, a line chart will be just as accurate as a XY Scatter chart. Or, if you use dates on the x-axis and leave a hole for the score for a week when the team doesn't play, by default XL will use a 'time scale' for the x-axis in a Line chart. In this case too the trendline will be accurate. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I have been trying to analyse some non financial data with Excel. I did pay to do an Excel course of several weeks, but they only touched upon accounting uses but nothing on statistics. I am very much a novice with Excel. I have beening doing line graphs combined with trendlines. I recently read that trendlines are not accurate unless scatterplots are used. So can someone give me a rough guesstimate what the range of that inaccuracy might be? And a very brief idea of what might cause that inaccuracy? TIA, Nev. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|