Trend line through the origin

  • Thread starter Thread starter Alan Cocks
  • Start date Start date
A

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
 
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
 
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
 
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
 
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
 
Back
Top