+ Reply to Thread
Results 1 to 14 of 14

Linear trendline:wrong equation

  1. #1
    Jan M.
    Guest

    Linear trendline:wrong equation

    Hi,

    I created a bar chart in Excel from the following data:

    X Y
    73 6.6
    78 5.7
    86 4.8

    The SLOPE and the INTERCEPT functions returned -0.136 and 16.4448
    respectively. The resulting equation is Y = -0.136X + 16.448 which seems good
    enough to me.

    Then I added a linear trendline to the chart. Excel displayed the following
    equation: Y = -0.9X + 7.5, R ^2 =1 which is way off (and it's not a rounding
    problem)!!!

    The data seemed farly linear to me, how come Excel can't come up with the
    right equation???

    Thanks

    Jan M.



  2. #2
    Jerry W. Lewis
    Guest

    RE: Linear trendline:wrong equation

    Use an "XY (Scatter)" chart.

    When you selected a "Line" chart, you (by definition) told Excel that your
    x-axis was categorical instead of numeric, and that what you provided for the
    x-axis was a set of category labels that may or may not have numeric values.
    Why Excel would offer to fit a trendline in that circumstance is a mystery to
    me, but when it does, it uses x-values of 1,2,3,... and correctly calculates
    the regression of y against those assumed x-values.

    Jerry

    "Jan M." wrote:

    > Hi,
    >
    > I created a bar chart in Excel from the following data:
    >
    > X Y
    > 73 6.6
    > 78 5.7
    > 86 4.8
    >
    > The SLOPE and the INTERCEPT functions returned -0.136 and 16.4448
    > respectively. The resulting equation is Y = -0.136X + 16.448 which seems good
    > enough to me.
    >
    > Then I added a linear trendline to the chart. Excel displayed the following
    > equation: Y = -0.9X + 7.5, R ^2 =1 which is way off (and it's not a rounding
    > problem)!!!
    >
    > The data seemed farly linear to me, how come Excel can't come up with the
    > right equation???
    >
    > Thanks
    >
    > Jan M.
    >
    >


  3. #3
    Jerry W. Lewis
    Guest

    RE: Linear trendline:wrong equation

    Similarly, the bar and column charts assume that the x-axis is non-numerica
    categories to be treated as 1,2,3,... if a trendline is requested.

    Excel provides no chart type that plots bar heights/lengths against a
    numberic axis.

    Jerry

    "Jerry W. Lewis" wrote:

    > Use an "XY (Scatter)" chart.
    >
    > When you selected a "Line" chart, you (by definition) told Excel that your
    > x-axis was categorical instead of numeric, and that what you provided for the
    > x-axis was a set of category labels that may or may not have numeric values.
    > Why Excel would offer to fit a trendline in that circumstance is a mystery to
    > me, but when it does, it uses x-values of 1,2,3,... and correctly calculates
    > the regression of y against those assumed x-values.
    >
    > Jerry
    >
    > "Jan M." wrote:
    >
    > > Hi,
    > >
    > > I created a bar chart in Excel from the following data:
    > >
    > > X Y
    > > 73 6.6
    > > 78 5.7
    > > 86 4.8
    > >
    > > The SLOPE and the INTERCEPT functions returned -0.136 and 16.4448
    > > respectively. The resulting equation is Y = -0.136X + 16.448 which seems good
    > > enough to me.
    > >
    > > Then I added a linear trendline to the chart. Excel displayed the following
    > > equation: Y = -0.9X + 7.5, R ^2 =1 which is way off (and it's not a rounding
    > > problem)!!!
    > >
    > > The data seemed farly linear to me, how come Excel can't come up with the
    > > right equation???
    > >
    > > Thanks
    > >
    > > Jan M.
    > >
    > >


  4. #4
    Jan M.
    Guest

    RE: Linear trendline:wrong equation

    Jerry,

    thanks for your reply!

    You are right about excel using 1,2,3 as x-values: I hadn't figured that one
    out!

    What do you mean by an "XY (Scatter)" chart? I'm still missing something
    here: What should I do differently to have excel give me the right equation?

    Thanks

    "Jerry W. Lewis" wrote:

    > Use an "XY (Scatter)" chart.
    >
    > When you selected a "Line" chart, you (by definition) told Excel that your
    > x-axis was categorical instead of numeric, and that what you provided for the
    > x-axis was a set of category labels that may or may not have numeric values.
    > Why Excel would offer to fit a trendline in that circumstance is a mystery to
    > me, but when it does, it uses x-values of 1,2,3,... and correctly calculates
    > the regression of y against those assumed x-values.
    >
    > Jerry
    >
    > "Jan M." wrote:
    >
    > > Hi,
    > >
    > > I created a bar chart in Excel from the following data:
    > >
    > > X Y
    > > 73 6.6
    > > 78 5.7
    > > 86 4.8
    > >
    > > The SLOPE and the INTERCEPT functions returned -0.136 and 16.4448
    > > respectively. The resulting equation is Y = -0.136X + 16.448 which seems good
    > > enough to me.
    > >
    > > Then I added a linear trendline to the chart. Excel displayed the following
    > > equation: Y = -0.9X + 7.5, R ^2 =1 which is way off (and it's not a rounding
    > > problem)!!!
    > >
    > > The data seemed farly linear to me, how come Excel can't come up with the
    > > right equation???
    > >
    > > Thanks
    > >
    > > Jan M.
    > >
    > >


  5. #5
    Jan M.
    Guest

    RE: Linear trendline:wrong equation

    Jerry,

    I think I've figured out what your meant by "xy scatter"!
    I modified my data table the following way and got the right results:

    X Y
    73 6.6
    74
    75
    76
    77
    78 5.8
    .... ...

    Thanks for your help.

    Jan M.



    "Jerry W. Lewis" wrote:

    > Use an "XY (Scatter)" chart.
    >
    > When you selected a "Line" chart, you (by definition) told Excel that your
    > x-axis was categorical instead of numeric, and that what you provided for the
    > x-axis was a set of category labels that may or may not have numeric values.
    > Why Excel would offer to fit a trendline in that circumstance is a mystery to
    > me, but when it does, it uses x-values of 1,2,3,... and correctly calculates
    > the regression of y against those assumed x-values.
    >
    > Jerry
    >
    > "Jan M." wrote:
    >
    > > Hi,
    > >
    > > I created a bar chart in Excel from the following data:
    > >
    > > X Y
    > > 73 6.6
    > > 78 5.7
    > > 86 4.8
    > >
    > > The SLOPE and the INTERCEPT functions returned -0.136 and 16.4448
    > > respectively. The resulting equation is Y = -0.136X + 16.448 which seems good
    > > enough to me.
    > >
    > > Then I added a linear trendline to the chart. Excel displayed the following
    > > equation: Y = -0.9X + 7.5, R ^2 =1 which is way off (and it's not a rounding
    > > problem)!!!
    > >
    > > The data seemed farly linear to me, how come Excel can't come up with the
    > > right equation???
    > >
    > > Thanks
    > >
    > > Jan M.
    > >
    > >


  6. #6
    Jon Peltier
    Guest

    Re: Linear trendline:wrong equation

    What Jerry means is don't use a column chart. It is the wrong kind of chart
    to use to show relationships between two variables, even if you are not
    trying to generate statistics on it. Rebuild the chart and select one of the
    XY subtypes in step 1 of the chart wizard, or convert the chart using Chart
    Type on the Chart menu. There's no need to mess around with the data range.

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______

    "Jan M." <[email protected]> wrote in message
    news:[email protected]...
    > Jerry,
    >
    > I think I've figured out what your meant by "xy scatter"!
    > I modified my data table the following way and got the right results:
    >
    > X Y
    > 73 6.6
    > 74
    > 75
    > 76
    > 77
    > 78 5.8
    > ... ...
    >
    > Thanks for your help.
    >
    > Jan M.
    >
    >
    >
    > "Jerry W. Lewis" wrote:
    >
    >> Use an "XY (Scatter)" chart.
    >>
    >> When you selected a "Line" chart, you (by definition) told Excel that
    >> your
    >> x-axis was categorical instead of numeric, and that what you provided for
    >> the
    >> x-axis was a set of category labels that may or may not have numeric
    >> values.
    >> Why Excel would offer to fit a trendline in that circumstance is a
    >> mystery to
    >> me, but when it does, it uses x-values of 1,2,3,... and correctly
    >> calculates
    >> the regression of y against those assumed x-values.
    >>
    >> Jerry
    >>
    >> "Jan M." wrote:
    >>
    >> > Hi,
    >> >
    >> > I created a bar chart in Excel from the following data:
    >> >
    >> > X Y
    >> > 73 6.6
    >> > 78 5.7
    >> > 86 4.8
    >> >
    >> > The SLOPE and the INTERCEPT functions returned -0.136 and 16.4448
    >> > respectively. The resulting equation is Y = -0.136X + 16.448 which
    >> > seems good
    >> > enough to me.
    >> >
    >> > Then I added a linear trendline to the chart. Excel displayed the
    >> > following
    >> > equation: Y = -0.9X + 7.5, R ^2 =1 which is way off (and it's not a
    >> > rounding
    >> > problem)!!!
    >> >
    >> > The data seemed farly linear to me, how come Excel can't come up with
    >> > the
    >> > right equation???
    >> >
    >> > Thanks
    >> >
    >> > Jan M.
    >> >
    >> >




  7. #7
    Jan M.
    Guest

    Re: Linear trendline:wrong equation

    Jon,

    thanks for your input.

    I've tried changing the chart type with no results. What is it that you call
    an XY subtype? It may seems trivial to you, but I assure you that it is not
    to me (maybe because I'm using a french version).

    Thanks.

    Jan M.


    "Jon Peltier" wrote:

    > What Jerry means is don't use a column chart. It is the wrong kind of chart
    > to use to show relationships between two variables, even if you are not
    > trying to generate statistics on it. Rebuild the chart and select one of the
    > XY subtypes in step 1 of the chart wizard, or convert the chart using Chart
    > Type on the Chart menu. There's no need to mess around with the data range.
    >
    > - Jon
    > -------
    > Jon Peltier, Microsoft Excel MVP
    > Peltier Technical Services
    > Tutorials and Custom Solutions
    > http://PeltierTech.com/
    > _______
    >
    > "Jan M." <[email protected]> wrote in message
    > news:[email protected]...
    > > Jerry,
    > >
    > > I think I've figured out what your meant by "xy scatter"!
    > > I modified my data table the following way and got the right results:
    > >
    > > X Y
    > > 73 6.6
    > > 74
    > > 75
    > > 76
    > > 77
    > > 78 5.8
    > > ... ...
    > >
    > > Thanks for your help.
    > >
    > > Jan M.
    > >
    > >
    > >
    > > "Jerry W. Lewis" wrote:
    > >
    > >> Use an "XY (Scatter)" chart.
    > >>
    > >> When you selected a "Line" chart, you (by definition) told Excel that
    > >> your
    > >> x-axis was categorical instead of numeric, and that what you provided for
    > >> the
    > >> x-axis was a set of category labels that may or may not have numeric
    > >> values.
    > >> Why Excel would offer to fit a trendline in that circumstance is a
    > >> mystery to
    > >> me, but when it does, it uses x-values of 1,2,3,... and correctly
    > >> calculates
    > >> the regression of y against those assumed x-values.
    > >>
    > >> Jerry
    > >>
    > >> "Jan M." wrote:
    > >>
    > >> > Hi,
    > >> >
    > >> > I created a bar chart in Excel from the following data:
    > >> >
    > >> > X Y
    > >> > 73 6.6
    > >> > 78 5.7
    > >> > 86 4.8
    > >> >
    > >> > The SLOPE and the INTERCEPT functions returned -0.136 and 16.4448
    > >> > respectively. The resulting equation is Y = -0.136X + 16.448 which
    > >> > seems good
    > >> > enough to me.
    > >> >
    > >> > Then I added a linear trendline to the chart. Excel displayed the
    > >> > following
    > >> > equation: Y = -0.9X + 7.5, R ^2 =1 which is way off (and it's not a
    > >> > rounding
    > >> > problem)!!!
    > >> >
    > >> > The data seemed farly linear to me, how come Excel can't come up with
    > >> > the
    > >> > right equation???
    > >> >
    > >> > Thanks
    > >> >
    > >> > Jan M.
    > >> >
    > >> >

    >
    >
    >


  8. #8
    Jon Peltier
    Guest

    Re: Linear trendline:wrong equation

    Okay, I'll try to type more slowly. That's a joke.

    In step 1 of the Chart Wizard, or in the Chart Type dialog, the top five
    chart types in the left list are Column, Bar, Line, Pie, and XY. Select the
    fifth (XY) as the chart type. Even in the French version, I'm sure the icons
    are in the same order.

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______

    "Jan M." <[email protected]> wrote in message
    news:[email protected]...
    > Jon,
    >
    > thanks for your input.
    >
    > I've tried changing the chart type with no results. What is it that you
    > call
    > an XY subtype? It may seems trivial to you, but I assure you that it is
    > not
    > to me (maybe because I'm using a french version).
    >
    > Thanks.
    >
    > Jan M.
    >
    >
    > "Jon Peltier" wrote:
    >
    >> What Jerry means is don't use a column chart. It is the wrong kind of
    >> chart
    >> to use to show relationships between two variables, even if you are not
    >> trying to generate statistics on it. Rebuild the chart and select one of
    >> the
    >> XY subtypes in step 1 of the chart wizard, or convert the chart using
    >> Chart
    >> Type on the Chart menu. There's no need to mess around with the data
    >> range.
    >>
    >> - Jon
    >> -------
    >> Jon Peltier, Microsoft Excel MVP
    >> Peltier Technical Services
    >> Tutorials and Custom Solutions
    >> http://PeltierTech.com/
    >> _______
    >>
    >> "Jan M." <[email protected]> wrote in message
    >> news:[email protected]...
    >> > Jerry,
    >> >
    >> > I think I've figured out what your meant by "xy scatter"!
    >> > I modified my data table the following way and got the right results:
    >> >
    >> > X Y
    >> > 73 6.6
    >> > 74
    >> > 75
    >> > 76
    >> > 77
    >> > 78 5.8
    >> > ... ...
    >> >
    >> > Thanks for your help.
    >> >
    >> > Jan M.
    >> >
    >> >
    >> >
    >> > "Jerry W. Lewis" wrote:
    >> >
    >> >> Use an "XY (Scatter)" chart.
    >> >>
    >> >> When you selected a "Line" chart, you (by definition) told Excel that
    >> >> your
    >> >> x-axis was categorical instead of numeric, and that what you provided
    >> >> for
    >> >> the
    >> >> x-axis was a set of category labels that may or may not have numeric
    >> >> values.
    >> >> Why Excel would offer to fit a trendline in that circumstance is a
    >> >> mystery to
    >> >> me, but when it does, it uses x-values of 1,2,3,... and correctly
    >> >> calculates
    >> >> the regression of y against those assumed x-values.
    >> >>
    >> >> Jerry
    >> >>
    >> >> "Jan M." wrote:
    >> >>
    >> >> > Hi,
    >> >> >
    >> >> > I created a bar chart in Excel from the following data:
    >> >> >
    >> >> > X Y
    >> >> > 73 6.6
    >> >> > 78 5.7
    >> >> > 86 4.8
    >> >> >
    >> >> > The SLOPE and the INTERCEPT functions returned -0.136 and 16.4448
    >> >> > respectively. The resulting equation is Y = -0.136X + 16.448 which
    >> >> > seems good
    >> >> > enough to me.
    >> >> >
    >> >> > Then I added a linear trendline to the chart. Excel displayed the
    >> >> > following
    >> >> > equation: Y = -0.9X + 7.5, R ^2 =1 which is way off (and it's not a
    >> >> > rounding
    >> >> > problem)!!!
    >> >> >
    >> >> > The data seemed farly linear to me, how come Excel can't come up
    >> >> > with
    >> >> > the
    >> >> > right equation???
    >> >> >
    >> >> > Thanks
    >> >> >
    >> >> > Jan M.
    >> >> >
    >> >> >

    >>
    >>
    >>




  9. #9
    Jan M.
    Guest

    Re: Linear trendline:wrong equation

    Jon and Jerry,

    I've found the XY (Scatter)! In a french version it is called "Nuages de
    points" (Clouds of dots). Very poetic isn't it?

    Thanks to both of you.

    Jan M.



    "Jon Peltier" wrote:

    > What Jerry means is don't use a column chart. It is the wrong kind of chart
    > to use to show relationships between two variables, even if you are not
    > trying to generate statistics on it. Rebuild the chart and select one of the
    > XY subtypes in step 1 of the chart wizard, or convert the chart using Chart
    > Type on the Chart menu. There's no need to mess around with the data range.
    >
    > - Jon
    > -------
    > Jon Peltier, Microsoft Excel MVP
    > Peltier Technical Services
    > Tutorials and Custom Solutions
    > http://PeltierTech.com/
    > _______
    >
    > "Jan M." <[email protected]> wrote in message
    > news:[email protected]...
    > > Jerry,
    > >
    > > I think I've figured out what your meant by "xy scatter"!
    > > I modified my data table the following way and got the right results:
    > >
    > > X Y
    > > 73 6.6
    > > 74
    > > 75
    > > 76
    > > 77
    > > 78 5.8
    > > ... ...
    > >
    > > Thanks for your help.
    > >
    > > Jan M.
    > >
    > >
    > >
    > > "Jerry W. Lewis" wrote:
    > >
    > >> Use an "XY (Scatter)" chart.
    > >>
    > >> When you selected a "Line" chart, you (by definition) told Excel that
    > >> your
    > >> x-axis was categorical instead of numeric, and that what you provided for
    > >> the
    > >> x-axis was a set of category labels that may or may not have numeric
    > >> values.
    > >> Why Excel would offer to fit a trendline in that circumstance is a
    > >> mystery to
    > >> me, but when it does, it uses x-values of 1,2,3,... and correctly
    > >> calculates
    > >> the regression of y against those assumed x-values.
    > >>
    > >> Jerry
    > >>
    > >> "Jan M." wrote:
    > >>
    > >> > Hi,
    > >> >
    > >> > I created a bar chart in Excel from the following data:
    > >> >
    > >> > X Y
    > >> > 73 6.6
    > >> > 78 5.7
    > >> > 86 4.8
    > >> >
    > >> > The SLOPE and the INTERCEPT functions returned -0.136 and 16.4448
    > >> > respectively. The resulting equation is Y = -0.136X + 16.448 which
    > >> > seems good
    > >> > enough to me.
    > >> >
    > >> > Then I added a linear trendline to the chart. Excel displayed the
    > >> > following
    > >> > equation: Y = -0.9X + 7.5, R ^2 =1 which is way off (and it's not a
    > >> > rounding
    > >> > problem)!!!
    > >> >
    > >> > The data seemed farly linear to me, how come Excel can't come up with
    > >> > the
    > >> > right equation???
    > >> >
    > >> > Thanks
    > >> >
    > >> > Jan M.
    > >> >
    > >> >

    >
    >
    >


  10. #10
    Jon Peltier
    Guest

    Re: Linear trendline:wrong equation

    Very nice. Is that what Frenchmen call it, or is that just Microsoft's
    unique translation?

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______


    "Jan M." <[email protected]> wrote in message
    news:[email protected]...
    > Jon and Jerry,
    >
    > I've found the XY (Scatter)! In a french version it is called "Nuages de
    > points" (Clouds of dots). Very poetic isn't it?
    >
    > Thanks to both of you.
    >
    > Jan M.
    >
    >
    >
    > "Jon Peltier" wrote:
    >
    >> What Jerry means is don't use a column chart. It is the wrong kind of
    >> chart
    >> to use to show relationships between two variables, even if you are not
    >> trying to generate statistics on it. Rebuild the chart and select one of
    >> the
    >> XY subtypes in step 1 of the chart wizard, or convert the chart using
    >> Chart
    >> Type on the Chart menu. There's no need to mess around with the data
    >> range.
    >>
    >> - Jon
    >> -------
    >> Jon Peltier, Microsoft Excel MVP
    >> Peltier Technical Services
    >> Tutorials and Custom Solutions
    >> http://PeltierTech.com/
    >> _______
    >>
    >> "Jan M." <[email protected]> wrote in message
    >> news:[email protected]...
    >> > Jerry,
    >> >
    >> > I think I've figured out what your meant by "xy scatter"!
    >> > I modified my data table the following way and got the right results:
    >> >
    >> > X Y
    >> > 73 6.6
    >> > 74
    >> > 75
    >> > 76
    >> > 77
    >> > 78 5.8
    >> > ... ...
    >> >
    >> > Thanks for your help.
    >> >
    >> > Jan M.
    >> >
    >> >
    >> >
    >> > "Jerry W. Lewis" wrote:
    >> >
    >> >> Use an "XY (Scatter)" chart.
    >> >>
    >> >> When you selected a "Line" chart, you (by definition) told Excel that
    >> >> your
    >> >> x-axis was categorical instead of numeric, and that what you provided
    >> >> for
    >> >> the
    >> >> x-axis was a set of category labels that may or may not have numeric
    >> >> values.
    >> >> Why Excel would offer to fit a trendline in that circumstance is a
    >> >> mystery to
    >> >> me, but when it does, it uses x-values of 1,2,3,... and correctly
    >> >> calculates
    >> >> the regression of y against those assumed x-values.
    >> >>
    >> >> Jerry
    >> >>
    >> >> "Jan M." wrote:
    >> >>
    >> >> > Hi,
    >> >> >
    >> >> > I created a bar chart in Excel from the following data:
    >> >> >
    >> >> > X Y
    >> >> > 73 6.6
    >> >> > 78 5.7
    >> >> > 86 4.8
    >> >> >
    >> >> > The SLOPE and the INTERCEPT functions returned -0.136 and 16.4448
    >> >> > respectively. The resulting equation is Y = -0.136X + 16.448 which
    >> >> > seems good
    >> >> > enough to me.
    >> >> >
    >> >> > Then I added a linear trendline to the chart. Excel displayed the
    >> >> > following
    >> >> > equation: Y = -0.9X + 7.5, R ^2 =1 which is way off (and it's not a
    >> >> > rounding
    >> >> > problem)!!!
    >> >> >
    >> >> > The data seemed farly linear to me, how come Excel can't come up
    >> >> > with
    >> >> > the
    >> >> > right equation???
    >> >> >
    >> >> > Thanks
    >> >> >
    >> >> > Jan M.
    >> >> >
    >> >> >

    >>
    >>
    >>




  11. #11
    Jan M.
    Guest

    Re: Linear trendline:wrong equation

    Jon,

    I can't speak for others, but I'm not aware of anyone calling a graph a
    "cloud"!

    Jan.

    "Jon Peltier" wrote:

    > Very nice. Is that what Frenchmen call it, or is that just Microsoft's
    > unique translation?
    >
    > - Jon
    > -------
    > Jon Peltier, Microsoft Excel MVP
    > Peltier Technical Services
    > Tutorials and Custom Solutions
    > http://PeltierTech.com/
    > _______
    >
    >
    > "Jan M." <[email protected]> wrote in message
    > news:[email protected]...
    > > Jon and Jerry,
    > >
    > > I've found the XY (Scatter)! In a french version it is called "Nuages de
    > > points" (Clouds of dots). Very poetic isn't it?
    > >
    > > Thanks to both of you.
    > >
    > > Jan M.
    > >
    > >
    > >
    > > "Jon Peltier" wrote:
    > >
    > >> What Jerry means is don't use a column chart. It is the wrong kind of
    > >> chart
    > >> to use to show relationships between two variables, even if you are not
    > >> trying to generate statistics on it. Rebuild the chart and select one of
    > >> the
    > >> XY subtypes in step 1 of the chart wizard, or convert the chart using
    > >> Chart
    > >> Type on the Chart menu. There's no need to mess around with the data
    > >> range.
    > >>
    > >> - Jon
    > >> -------
    > >> Jon Peltier, Microsoft Excel MVP
    > >> Peltier Technical Services
    > >> Tutorials and Custom Solutions
    > >> http://PeltierTech.com/
    > >> _______
    > >>
    > >> "Jan M." <[email protected]> wrote in message
    > >> news:[email protected]...
    > >> > Jerry,
    > >> >
    > >> > I think I've figured out what your meant by "xy scatter"!
    > >> > I modified my data table the following way and got the right results:
    > >> >
    > >> > X Y
    > >> > 73 6.6
    > >> > 74
    > >> > 75
    > >> > 76
    > >> > 77
    > >> > 78 5.8
    > >> > ... ...
    > >> >
    > >> > Thanks for your help.
    > >> >
    > >> > Jan M.
    > >> >
    > >> >
    > >> >
    > >> > "Jerry W. Lewis" wrote:
    > >> >
    > >> >> Use an "XY (Scatter)" chart.
    > >> >>
    > >> >> When you selected a "Line" chart, you (by definition) told Excel that
    > >> >> your
    > >> >> x-axis was categorical instead of numeric, and that what you provided
    > >> >> for
    > >> >> the
    > >> >> x-axis was a set of category labels that may or may not have numeric
    > >> >> values.
    > >> >> Why Excel would offer to fit a trendline in that circumstance is a
    > >> >> mystery to
    > >> >> me, but when it does, it uses x-values of 1,2,3,... and correctly
    > >> >> calculates
    > >> >> the regression of y against those assumed x-values.
    > >> >>
    > >> >> Jerry
    > >> >>
    > >> >> "Jan M." wrote:
    > >> >>
    > >> >> > Hi,
    > >> >> >
    > >> >> > I created a bar chart in Excel from the following data:
    > >> >> >
    > >> >> > X Y
    > >> >> > 73 6.6
    > >> >> > 78 5.7
    > >> >> > 86 4.8
    > >> >> >
    > >> >> > The SLOPE and the INTERCEPT functions returned -0.136 and 16.4448
    > >> >> > respectively. The resulting equation is Y = -0.136X + 16.448 which
    > >> >> > seems good
    > >> >> > enough to me.
    > >> >> >
    > >> >> > Then I added a linear trendline to the chart. Excel displayed the
    > >> >> > following
    > >> >> > equation: Y = -0.9X + 7.5, R ^2 =1 which is way off (and it's not a
    > >> >> > rounding
    > >> >> > problem)!!!
    > >> >> >
    > >> >> > The data seemed farly linear to me, how come Excel can't come up
    > >> >> > with
    > >> >> > the
    > >> >> > right equation???
    > >> >> >
    > >> >> > Thanks
    > >> >> >
    > >> >> > Jan M.
    > >> >> >
    > >> >> >
    > >>
    > >>
    > >>

    >
    >
    >


  12. #12
    Jon Peltier
    Guest

    Re: Linear trendline:wrong equation

    What's the word for snow, "neige"? Maybe that's the word they wanted, thing
    flurry instead of scatter.

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______

    "Jan M." <[email protected]> wrote in message
    news:[email protected]...
    > Jon,
    >
    > I can't speak for others, but I'm not aware of anyone calling a graph a
    > "cloud"!
    >
    > Jan.
    >
    > "Jon Peltier" wrote:
    >
    >> Very nice. Is that what Frenchmen call it, or is that just Microsoft's
    >> unique translation?
    >>
    >> - Jon
    >> -------
    >> Jon Peltier, Microsoft Excel MVP
    >> Peltier Technical Services
    >> Tutorials and Custom Solutions
    >> http://PeltierTech.com/
    >> _______
    >>
    >>
    >> "Jan M." <[email protected]> wrote in message
    >> news:[email protected]...
    >> > Jon and Jerry,
    >> >
    >> > I've found the XY (Scatter)! In a french version it is called "Nuages
    >> > de
    >> > points" (Clouds of dots). Very poetic isn't it?
    >> >
    >> > Thanks to both of you.
    >> >
    >> > Jan M.
    >> >
    >> >
    >> >
    >> > "Jon Peltier" wrote:
    >> >
    >> >> What Jerry means is don't use a column chart. It is the wrong kind of
    >> >> chart
    >> >> to use to show relationships between two variables, even if you are
    >> >> not
    >> >> trying to generate statistics on it. Rebuild the chart and select one
    >> >> of
    >> >> the
    >> >> XY subtypes in step 1 of the chart wizard, or convert the chart using
    >> >> Chart
    >> >> Type on the Chart menu. There's no need to mess around with the data
    >> >> range.
    >> >>
    >> >> - Jon
    >> >> -------
    >> >> Jon Peltier, Microsoft Excel MVP
    >> >> Peltier Technical Services
    >> >> Tutorials and Custom Solutions
    >> >> http://PeltierTech.com/
    >> >> _______
    >> >>
    >> >> "Jan M." <[email protected]> wrote in message
    >> >> news:[email protected]...
    >> >> > Jerry,
    >> >> >
    >> >> > I think I've figured out what your meant by "xy scatter"!
    >> >> > I modified my data table the following way and got the right
    >> >> > results:
    >> >> >
    >> >> > X Y
    >> >> > 73 6.6
    >> >> > 74
    >> >> > 75
    >> >> > 76
    >> >> > 77
    >> >> > 78 5.8
    >> >> > ... ...
    >> >> >
    >> >> > Thanks for your help.
    >> >> >
    >> >> > Jan M.
    >> >> >
    >> >> >
    >> >> >
    >> >> > "Jerry W. Lewis" wrote:
    >> >> >
    >> >> >> Use an "XY (Scatter)" chart.
    >> >> >>
    >> >> >> When you selected a "Line" chart, you (by definition) told Excel
    >> >> >> that
    >> >> >> your
    >> >> >> x-axis was categorical instead of numeric, and that what you
    >> >> >> provided
    >> >> >> for
    >> >> >> the
    >> >> >> x-axis was a set of category labels that may or may not have
    >> >> >> numeric
    >> >> >> values.
    >> >> >> Why Excel would offer to fit a trendline in that circumstance is a
    >> >> >> mystery to
    >> >> >> me, but when it does, it uses x-values of 1,2,3,... and correctly
    >> >> >> calculates
    >> >> >> the regression of y against those assumed x-values.
    >> >> >>
    >> >> >> Jerry
    >> >> >>
    >> >> >> "Jan M." wrote:
    >> >> >>
    >> >> >> > Hi,
    >> >> >> >
    >> >> >> > I created a bar chart in Excel from the following data:
    >> >> >> >
    >> >> >> > X Y
    >> >> >> > 73 6.6
    >> >> >> > 78 5.7
    >> >> >> > 86 4.8
    >> >> >> >
    >> >> >> > The SLOPE and the INTERCEPT functions returned -0.136 and 16.4448
    >> >> >> > respectively. The resulting equation is Y = -0.136X + 16.448
    >> >> >> > which
    >> >> >> > seems good
    >> >> >> > enough to me.
    >> >> >> >
    >> >> >> > Then I added a linear trendline to the chart. Excel displayed the
    >> >> >> > following
    >> >> >> > equation: Y = -0.9X + 7.5, R ^2 =1 which is way off (and it's not
    >> >> >> > a
    >> >> >> > rounding
    >> >> >> > problem)!!!
    >> >> >> >
    >> >> >> > The data seemed farly linear to me, how come Excel can't come up
    >> >> >> > with
    >> >> >> > the
    >> >> >> > right equation???
    >> >> >> >
    >> >> >> > Thanks
    >> >> >> >
    >> >> >> > Jan M.
    >> >> >> >
    >> >> >> >
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>




  13. #13
    Registered User
    Join Date
    09-28-2016
    Location
    USA
    MS-Off Ver
    2010
    Posts
    1

    Re: Linear trendline:wrong equation

    Oh my GOD. Thank you. I have been struggling with this for weeks now. I registered on this site just to say thank you.


    Quote Originally Posted by Jerry W. Lewis View Post
    Use an "XY (Scatter)" chart.

    When you selected a "Line" chart, you (by definition) told Excel that your
    x-axis was categorical instead of numeric, and that what you provided for the
    x-axis was a set of category labels that may or may not have numeric values.
    Why Excel would offer to fit a trendline in that circumstance is a mystery to
    me, but when it does, it uses x-values of 1,2,3,... and correctly calculates
    the regression of y against those assumed x-values.

    Jerry

    "Jan M." wrote:

    > Hi,
    >
    > I created a bar chart in Excel from the following data:
    >
    > X Y
    > 73 6.6
    > 78 5.7
    > 86 4.8
    >
    > The SLOPE and the INTERCEPT functions returned -0.136 and 16.4448
    > respectively. The resulting equation is Y = -0.136X + 16.448 which seems good
    > enough to me.
    >
    > Then I added a linear trendline to the chart. Excel displayed the following
    > equation: Y = -0.9X + 7.5, R ^2 =1 which is way off (and it's not a rounding
    > problem)!!!
    >
    > The data seemed farly linear to me, how come Excel can't come up with the
    > right equation???
    >
    > Thanks
    >
    > Jan M.
    >
    >

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,906

    Re: Linear trendline:wrong equation

    dboyallstars , welcome to the forum

    Thank you for the feedback, always appreciated
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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