Trend line through the origin

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

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

Jerry

Alan Cocks wrote:

> 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

"Alan Cocks" <alandrob@netspace.net.au> wrote in message
news:bdrtl3$11rh$1@otis.netspace.net.au...
> 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
>
>

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" <post_a_reply@no_e-mail.com> wrote in message
news:3F017CA3.4090002@no_e-mail.com...
> See help for LINEST, or use
> =SUMPRODUCT(ydata,xdata)/SUMSQ(xdata)
>
> Jerry
>
> Alan Cocks wrote:
>
> > 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
>

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 wrote:

> 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" <post_a_reply@no_e-mail.com> wrote in message
> news:3F017CA3.4090002@no_e-mail.com...
>
>>See help for LINEST, or use
>> =SUMPRODUCT(ydata,xdata)/SUMSQ(xdata)
>>
>>Jerry
>>
>>Alan Cocks wrote:
>>
>>
>>>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
>>>
>
>

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


"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
news:3F0216B0.4050003@no_e-mail.com...
> 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 wrote:
>
> > 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" <post_a_reply@no_e-mail.com> wrote in message
> > news:3F017CA3.4090002@no_e-mail.com...
> >
> >>See help for LINEST, or use
> >> =SUMPRODUCT(ydata,xdata)/SUMSQ(xdata)
> >>
> >>Jerry
> >>
> >>Alan Cocks wrote:
> >>
> >>
> >>>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
> >>>
> >
> >
>