Forum Discussion

Su2535's avatar
Su2535
Copper Contributor
Mar 16, 2023

linear function

What is the formula for

if x=5 y=1

if x=10 y=3

if x=15 y=5

if x=12,5 what is the value of y

  • JoeUser2004's avatar
    JoeUser2004
    Bronze Contributor

    (resubmitted to correct critical typo)

     

    Su2535 

     

    At the risk of doing your class or exam assignment for you, use LINEST to determine the coefficients of the linear approximation est y = m*x + b.

     

    Formulas:

    F3:G5:  =LINEST(B3:B5, A3:A5, TRUE, TRUE)

    C3:       =A3*$F$3 + $B$3

     

    Select F3:G5 and type the formula.  In some versions of Excel, we must commit by pressing ctrl+shift+Enter instead of just Enter.

     

    In this case, the linear "approximation" is an exact fit.  This is indicated by r^2 = 1 in F5.

     

    PS....  In general, it is unwise to use LINEST results blindly.  The first step should be to use an XY Scatter chart to visually inspect the relationship between x and y.  You can even create a (linear) trendline to determine the fit.

Resources