Share via

Excel: Why do regression and trend lines display non-identical formulas?

Anonymous
2022-02-10T21:06:52+00:00

Please advise,

As seen below, the regression line follows the formula Y=-2X+20, which enables all coordinates, like (5,10), to suit both the graph and the formula.

On the other hand, the trend line which follows the graph by 100%, with R^2=1, presents a diferent formula of Y=-2X+22, which does not suit the coordinates like (5,10) though by the graph it looks as if it does.

The question is, why?

Microsoft 365 and Office | Excel | For education | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2022-02-10T22:18:37+00:00

    Hi. -2X+22 comes from a line chart.

    You want to use a Scatter chart.

    2 people found this answer helpful.
    0 comments
Answer accepted by question author
  1. Anonymous
    2022-02-11T05:10:23+00:00

    Hi MickeyHargaash

    Thanks for posting the thread on our forum.

    According to your description, it seems that the regression and trend lines not in identical fomulas.

    We did a test on our end, if we select the line chart, the regression and trend lines will display as yours.

    If we select scatter chart, it will display the identical formulas between regression and trend lines .

    So we suggest you select Insert>Scatter chart to check the result.

    Hope the suggestion will help you.

    Regards,

    Stacey

    1 person found this answer helpful.
    0 comments
Answer accepted by question author
  1. HansV 462.4K Reputation points MVP Volunteer Moderator
    2022-02-10T23:05:36+00:00

    Rand2201 has hit the nail on the head. For a line chart, the x-values are treated as text, and the trend line uses 1, 2, 3. ... instead of the displayed x-axis values.

    The chart in my screenshot is a XY scatter chart.

    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.4K Reputation points MVP Volunteer Moderator
    2022-02-10T21:14:39+00:00

    Weird - I get this:

    0 comments
  2. Anonymous
    2022-02-10T22:30:06+00:00

    Hi Hans,

    Thank you for the swift response.
    It is weird that we get different results.

    Maybe a different view will help.

    Regardless of the graphs in paticular, maybe the different results issue is something that you are familiar with?

    Mickey.

    0 comments