Trend line through the origin

[Alan Cocks]

I have three points (sometimes four) on a scatter graph.
I need the formula for the best fit straight line through them that also
goes exactly through the origin (0,0).

an example of data would be:

Test 1 X = 202.64, Y = 226.59
Test 2 X = 301.34, Y = 335.99
Test 3 X = 400.03, Y = 450.45

Alan

 

[Jerry W. Lewis]

See help for LINEST, or use
=SUMPRODUCT(ydata,xdata)/SUMSQ(xdata)

Jerry

 

[Bernard V Liengme]

With the x values in A2:A4, and the y-values in B2:B4 use
=LINEST(B2:B4,A2:A4,FALSE)
Since there is just one value, there is no need to make it any array formula
so complete with a simple Enter.

Best wishes
Bernard

 

[Alan Cocks]

Thanks Jerry,

But if I use LINEST with the three points, the trend line doesn't go through
0,0.

If I use the three points plus 0,0 it gets closer but still doesn't go
through 0,0.

I need one that goes through 0,0 exactly and is best fit for the other 3 or
4 points.

Alan

 

[Jerry W. Lewis]

LINEST has an option to force the line through (0,0). That and other
options are clearly documented in Help for LINEST. I also provided you
with an explicit formula for the slope of least squares line forced
through (0,0). If neither answer is satisfactory, then you need to
provide more information on why they are unsatisfactory.

Jerry

 

[Alan Cocks]

Thanks Jerry, Bernard & Mike!

for all your help.

You have cleared up some misunderstandings I had of the workings of LINEST
and it now works as I want it to.
The answers I get from here never cease to amaze me.

Alan