Custom Trendline

[memot]

Hello,

I have a scatter chart with X and Y axis values. And i need a trendline on it, but the problem is excel decides the curve of the trendline, i want to determine the curve. How could i do that?

y=mx+z (i want to determine the "m" manually.
I am also attached the example chart, on this chart i need my linear trendline's gradient must be 0.148608.

Thanks

 

[Somendra Misra]

Hi Memot,

I think it's not possible to change slope of linear trend Line but you can change y-intercept.

See the file, with Maroon Line.

Regards,

 

[Hui]

I would tackle this by adding a dummy set of data to the chart
and then manually playing with the M & C values to see what happens


You could also add stats to the Charts to get the Gradient and Intercept values for both sets of data using the Intercept() and Slope() functions

See attached file:

 

[memot]

I would tackle this by adding a dummy set of data to the chart
and then manually playing with the M & C values to see what happens
View attachment 9385

You could also add stats to the Charts to get the Gradient and Intercept values for both sets of data using the Intercept() and Slope() functions

See attached file:

Hey Hui can you please check your attached file please, i couldn't reach it.

 

[Hui]

Whoops, I attached a blank file ?

It's fixed now

 

[memot]

Hi Memot,

I think it's not possible to change slope of linear trend Line but you can change y-intercept.

See the file, with Maroon Line.

Regards,

Hey Somendra, Can you please explain the reason of your steps please? Your solution looking great and also intelligent. What's the idea here, mathematically i mean.

 

[Somendra Misra]

@memot

Equation of Straight line is y=mx+c.

In this m = slope you already provided, x are all given, so problem is to find c (y-intercept) which I found from below formula.

C = (SUM of all Y-Values-(Slope * SUM of all X-Value))/COUNT of Data point

So a new series is generated with the help of Y=mx+C as we have all the unknown now, and than plot it on graph.

Regards,

 

[memot]

@memot

Equation of Straight line is y=mx+c.

In this m = slope you already provided, x are all given, so problem is to find c (y-intercept) which I found from below formula.

C = (SUM of all Y-Values-(Slope * SUM of all X-Value))/COUNT of Data point

So a new series is generated with the help of Y=mx+C as we have all the unknown now, and than plot it on graph.

Regards,

Thank you very much somendra, i really mean it, you summarized very well, i got the point.

 

[memot]

Whoops, I attached a blank file ?

It's fixed now

Hey Hui, thank you very much for your support. But i have a question for your solution; we just make something out for the intercept right? there is no mathematically reason here, am i right? we are just drawing a line which's gradient is just what i wanted.

Edit: Hey hui, i just determined c=0 and set the intercept 0.0 on trendline, everything just looks simple and right. Thank you very much!

 

[memot]

@memot

Equation of Straight line is y=mx+c.

In this m = slope you already provided, x are all given, so problem is to find c (y-intercept) which I found from below formula.

C = (SUM of all Y-Values-(Slope * SUM of all X-Value))/COUNT of Data point

So a new series is generated with the help of Y=mx+C as we have all the unknown now, and than plot it on graph.

Regards,

Hey again Somendra, have another question for you; with your solution i made a line which has the exact slope like i wanted but this linear doesn't intercept with the origin, isn't that a problem? Or can i make my trendline move on the origin point?

I just don't understand the problem here, we are just applying the mathematical formula but how it's intercept point determined? in this way what was determined the intercept?

 

[Hui]

Memot

A straight line has a formula Y=MX+C

For your data the M= 0.2409 C=-7,200,000
Giving Y=0.2409X -7,200,000

That is when X=0, Y = -7,200,000
the next point is at Y = X*0.2409 - 7,200,000
where X is any value

If you require a line to have gradient M=0.2409 and X=0, Y=0, then all Y values will be 7,200,000 above (higher than) the trendline for the existing data
It will be parallel with the trendline for your data as it has the same gradient

 

[NARAYANK991]

Hi ,

Given that there is a mathematical basis for drawing a trendline , I don't think you should manipulate anything to get a trendline which suits you !

See this link for more information :

http://office.microsoft.com/en-nz/help/choosing-the-best-trendline-for-your-data-HP005262321.aspx

Narayan

 

[Hui]

Memot
As we have all said, there is logic and math behind a line of best fit
Typically it is called least squares approximation.
This involves determining a line in which the average distance from all points to the line is minimized. You can read about how it is done here:
http://hotmath.com/hotmath_help/topics/line-of-best-fit.html
http://en.wikipedia.org/wiki/Least_squares

If your real life situation is such that the Y intercept (C) cannot be below zero then there are techniques that you can use to model that.

The simplest is to select the Trendline you have above
Right Click, Format Trendline
Set to a Linear Trend type
Tick Set Intercept and make sure it is 0
Tick Show equation on Chart
Apply
Excel will show Y=0.1962X

this is the same as Y=0.1962X + 0
because the Y intercept is now set to 0

 

[memot]

Memot
As we have all said, there is logic and math behind a line of best fit
Typically it is called least squares approximation.
This involves determining a line in which the average distance from all points to the line is minimized. You can read about how it is done here:
http://hotmath.com/hotmath_help/topics/line-of-best-fit.html
http://en.wikipedia.org/wiki/Least_squares

If your real life situation is such that the Y intercept (C) cannot be below zero then there are techniques that you can use to model that.

The simplest is to select the Trendline you have above
Right Click, Format Trendline
Set to a Linear Trend type
Tick Set Intercept and make sure it is 0
Tick Show equation on Chart
Apply
Excel will show Y=0.1962X

this is the same as Y=0.1962X + 0
because the Y intercept is now set to 0
View attachment 9424

Hello Hui,
Thank you very much for your detailed answer, i got the point and checked the links you sent. I have resolved my problem with your solution (by changing the slope manually). And to remind you, i was looking for a solution which makes me able to change my slope. So your earlier answer fixed my problem.

Best