Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extract Trendline Equation (2000 sr1)

    I have an XY scatterpoint graph with a logarithmic trendline. The trendline equation is displayed in this form:

    y = a*ln(x) + b

    How can I, using either functions or VBA, get the "a" and "b" constants into cells?

    Thanks,
    Ken

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract Trendline Equation (2000 sr1)

    Say that your x values are in A1:A10, and your y values in B1:B10. In C1 (or in another free column), enter the formula

    =LN(A1)

    and fill down to C10. Select two empty cells next to each other, for example D1:E1. Enter the formula

    =LINEST(B1:B10,C1:C10)

    and confirm with Ctrl+Shift+Enter to make it an array formula. The first cell will contain the coefficient a and the second cell will contain the constant b. You can hide column C if you like.

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract Trendline Equation (2000 sr1)

    Thanks Hans! Works Great.!

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Extract Trendline Equation (2000 sr1)

    You could use, directly (again confirm with ctrl-shift-enter)
    <pre>=LINEST(B1:B10,LN(A1:A10))</pre>


    And not create the "intermediate column" at all.

    Steve

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract Trendline Equation (2000 sr1)

    Yep, thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •