Excel: Can I force a linear trendline through the origin?

[Bernard Liengme]

Yes, open at the Option tab when you make (or format) a trendline
There is a text box to set intercept to any value (including 0)
best wishes

 

[Bill]

I have done that, but it is not working. Perhaps it is because I am plotting
a log-log graph and therefore there is no actual zero value for the y-axis.
But I need the line to pass through the origin, and because the data covers
several decades, it needs to be plotted as log-log. Any ideas?

Bill

 

[Ali Baba]

Try this it may work
1) Display the equation of the line.
2) use the equation to calculate the value y by taking x as 0.0000001 or any
value
3) add these values (x,y) to your series as new points

you can then hide the marker if you want.


Hope this helps

 

[B. R.Ramachandran]

Hi,

If the plot 'looks' linear in the log-log plot, then the x,y-data are NOT
linear (and y =a* x^m). calculate the logarithms of the x and y values in
new columns and make a plot of log y vs log x, and get a trendline (which you
can force to pass through the origin; i.e., you are forcing "a" to be equal
to 1). However, note that the slope of this line is indeed the exponent "m"
in the equation, y = x^m.

Formatting the axes of a graph to logarithmic scales only changes the visual
appearance of the graph, but does not actually transform the x,y-data to
their logarithm values. If you still want to stick to the y vs x plot with
log-log scales(and not the log y vs log x plot), you can still get a
trendline that 'looks' linear by selecing "Power" and not "linear" for the
trendline type. The trendline equation will show up as y = a*x^m; but you
can't force "a" to become equal to 1. The latter can be done using Excel's
'Solver' utility.

Regards,
B. R. Ramachandran