How Excel computes RSQ for Trendlines - is there a BUG?

  • I have an Excel sheet (attached), with Months In col A and Sales in Column B.
    I have derived values for each of the trendlines using formulae.
    I also have Graphs for each trendline and the equation and RSQ(R2)
    X Values in column A= Months
    Y Values in column B = Sales

    Now the problem is

    1. RSQs generated by excel graph for exponential and power trendlines are not matching with the computed RSQ values in F32 and G 32 cells.
    2. Excel is computing RSQs of trendlines using formula - (RSQ(Known Ys,Known x's) I.e here Y is values of trendline and X is values of actual sales. That is how RSQs of Linear, Logarithmic and Polynomial Values are matching- Computed Vs. excel Graph generated.
    What is the problem?
    How does Excel Compute RSQ for Trendlines
    is it the RSQ of Trend line vales and Actual sales?
    Is there a bug in excel?
    Need help!

  • Re: How Excel computes RSQ for Trendlines - is there a BUG?


    Hi ksatyamahesh,
    Nice to meet you in this forum.
    First convert linear data then apply formula.
    Or try this function.
    power trend:
    =INDEX(LINEST(LN(B34:B105),LN(A34:A105),TRUE,TRUE),3,1)
    or
    =RSQ(LN(B34:B105),LN(A34:A105))
    exponential trend:
    =INDEX(LINEST(LN(B34:B105),A34:A105,TRUE,TRUE),3,1)
    or
    =RSQ(LN(B34:B105),A34:A105)
    Regards, junho

  • Re: How Excel computes RSQ for Trendlines - is there a BUG?


    I don't believe it is related to your question, but there are known issues with several of Excel's statistical functions, including RSQ which has been known to return negative values even though that should be impossible.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Re: How Excel computes RSQ for Trendlines - is there a BUG?


    Dear Junho,


    Bingo! Thanks a zillion. You are amazing!
    I was not able to crack this and had 2 sleepless nights.
    So, my mistake is in the formula of RSQ itself.


    Kindly give me formulas for computing RSQ of Linear Trend, Logarithmic Trend,Polynomial Trend also.


    Warm Regards
    Mahesh




  • Re: How Excel computes RSQ for Trendlines - is there a BUG?


    Dear Rory,
    Thank you for the comment.
    I am just a beginner with regards to stat functions of excel. However, the solution offered exactly fits my problem.

    Now,why a simple RSQ doesn’t work without the function LINEST (converting to linear data) is beyond my comprehension.

    I have done enough search on Google but nowhere could find an answer - How RSQs for various trendlines(Non-Linear) are to be computed using formulas.
    The answer given by Junho will be of immense help to many people searching for the answer.
    Junho had already provided formulae to compute RSQ for Power and Exponential trendlines.
    I have requested for formulae for LinearTrend, LogarithmicTrend and Polynomial Trend also.
    I am sure the answer offers complete solution to the problem posted.



    Warm Regards,
    K.Satya Mahesh

  • Re: How Excel computes RSQ for Trendlines - is there a BUG?


    Hi ksatyamahesh,
    Sorry for replying late.
    linear: y=ax+b : rsq(y,x)
    logarithmetic: y=aln(bx) --> exp(y)=exp(a)+bx : rsq(exp(y),x)
    polynomial : y=ax^2+bx+c : rsq(y,x^{1,2})
    Not sure foumulas i offered, but theory is convert fomula to linest form.
    Regards, junho

  • Re: How Excel computes RSQ for Trendlines - is there a BUG?


    Dear Junho,


    as suggested, for Logarithmic trend line I typed
    =RSQ((EXP(D34:D105)),(A34:A105))
    For Polynomial I typed
    =RSQ(E34:E105,((A34:A105)^{1,2}))


    These formulae did not work. May be I have keyed in wrong formulae.
    If there is any mistake kindly correct.
    Linear is OK but, Logarithmic and Polynomial not working.
    I would appreciate if you can type in formula like earlier reply.
    I see innumerable queries in different forums how to compute RSQ for Non Linear Trend lines but nowhere could find an answer.
    Possibly your answer could clarify many doubts.
    Thanks for bearing with my profound ignorance.
    Regards
    Mahesh


    PS. Just now I found an Formula for Logarithmic Trend line


    =RSQ(B34:B105,LN(A34:A105))
    This almost matches the Value of RSQ generated by the Graph

  • Re: How Excel computes RSQ for Trendlines - is there a BUG?


    Here are the final formula to use in Excel to compute trend line coefficients of Linear and Non-Linear trend lines


    I have gathered them from various Google searches.
    Junho , Kindly confirm that these formulae are correct.

    I am consolidating all the formule in one thread, so that it may be useful for someone searching for the same solution.

    I am thankful to Junho and Prof.Bernard Liengme.
    Sample excel sheet with formula is attached for ref.


    Source for chart trend lines formula is
    http://spreadsheetpage.com/ind…chart_trendline_formulas/
    http://people.stfx.ca/bliengme/
    http://people.stfx.ca/bliengme/excellinks.htm

    Linear Trend Equation

    X= Known X's (eg: Months on X Axis)
    Y= Known Y's (eg: Sales on Y Axis)

    Equation: y = m * x + b
    m: =SLOPE(y,x)
    b: =INTERCEPT(y,x)
    RSQ =RSQ(y,x)

    Logarithmic Trend Equation
    Equation: y = (c * LN(x)) + b
    c: =INDEX(LINEST(y,LN(x)),1)
    b: =INDEX(LINEST(y,LN(x)),1,2)
    RSQ =RSQ(y,LN(x))

    Power Trend - Equation
    Equation: y=c*x^b
    c: =EXP(INDEX(LINEST(LN(y),LN(x),,),1,2))
    b: =INDEX(LINEST(LN(y),LN(x),,),1)
    RSQ =INDEX(LINEST(LN(y),LN(x),TRUE,TRUE),3,1)

    Exponential Trend- Equation
    Equation: y = c *e ^(b * x)
    c: =EXP(INDEX(LINEST(LN(y),x),1,2))
    b: =INDEX(LINEST(LN(y),x),1)
    RSQ =RSQ(LN(y),x)

    2nd Order Polynomial Trend -Equation
    Equation: y = (c2 * x^2) + (c1 * x ^1) + b
    c2: =INDEX(LINEST(y,x^{1,2}),1)
    C1: =INDEX(LINEST(y,x^{1,2}),1,2)
    b = INDEX(LINEST(y,x^{1,2}),1,3)
    RSQ =INDEX(LINEST(y,x^{1,2},TRUE,TRUE),3,1)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!