When and Why Trendlines may not be calculated correctly?

  • Thread starter Thread starter Nev.
  • Start date Start date
N

Nev.

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.
 
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/
_______
 
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...
 
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
 
Back
Top