+ Reply to Thread
Results 1 to 4 of 4

When and Why Trendlines may not be calculated correctly?

  1. #1
    Nev.
    Guest

    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. #2
    Jon Peltier
    Guest

    Re: When and Why Trendlines may not be calculated correctly?

    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. #3
    Nev.
    Guest

    Re: When and Why Trendlines may not be calculated correctly?

    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. #4
    Tushar Mehta
    Guest

    Re: When and Why Trendlines may not be calculated correctly?

    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 <ONiK9JsFFHA.3336@TK2MSFTNGP10.phx.gbl>,
    nenvy@optusnet.com.au 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1