Posts by ksatyamahesh

    Re: Password Protect Sheets and Show sheets on Password - Macro

    Dear Sir,
    Thank You. There are so many links. I am sure That I Can figure out the solution.
    Thanks to You all I have learned to tweak some Macros.
    Smuzoen, Thanks for your suggestion. I understand That very sensitive information can not be considered to be kept in excel relying on security as I myself learned to break excel passwords with many tools that are available on the net.
    Thanks and Regards

    This is another Macro help I need. I sincerely appreciate the help!

    I have 3 Sheets one for each user - User 1,User 2 and User 3 with Data relevant to them
    I also have a control sheet which asks for User name and pass word
    in The control sheet i also Hide User names and pass words for Check
    If User name matches password The relevant sheet should be unhidden.

    I have attached the excel
    I need Macro That handles this.
    Thanks and Regards

    I need help on a simple Macro.
    The Logic is given below

    Start Macro
    Macro to start ob Click of a Button

    [COLOR="navy"]Cell A1= 0
    Wait for 3 Seconds
    Cell A1= Present Value+1
    Continue (Loop) to add1 Wait for 3 Seconds till It reaches Value of 12
    Once A1 reaches value to 12, reset it to Value of 1

    End Macro[/COLOR]

    Yesterday I requested for help and got it in few Hours. I understood the power of forums and the helping nature of people
    Thank you in advance

    I need help in writing a Macro for this simple problem.
    (I do not know how to write a Macro But Know how to use a Macro)

    I need to

    [COLOR="navy"]Print a the sheet ( Print area already set)
    If Value of cell A1=1 Else Print should be disabled(should not print and give an error message)

    Thanks in advance...

    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…chart_trendline_formulas/

    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)

    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)

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

    Dear Junho,

    as suggested, for Logarithmic trend line I typed
    For Polynomial I typed

    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.

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

    This almost matches the Value of RSQ generated by the Graph

    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?

    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

    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!