Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Aug 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Logarithmic Trendline (Windows 2000 Pro)

    I have a table consisting of the following info: (1) 1st column listing years 2000-2003; (2) 2nd column listing home sales for each of those years. I created a line chart with the Years on the X-axis and # home sales on the Y-axis. I then created a trendline using the logarithmic "type" going out 4 years to Year 2007. However, I want the forecasted values for Years 2004-2007 to appear in the table. I have tried all sorts of equations using LINEST and LOGEST and cannot replicate the values that are represented by the trendline in the chart. How can I do this? I look forward to your response. (By the way, I have not had a problem with creating a linear trendline and calculating the forecasted linear values, but everytime that I have attempted using LINEST or LOGEST, i get some nonsensical result.) I look forward to your help.

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

    Re: Logarithmic Trendline (Windows 2000 Pro)

    This is possible, but it would be helpful if you could post a small sample workbook.

  3. #3
    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: Logarithmic Trendline (Windows 2000 Pro)

    Linest is for linear trends, logest is for exponential (not logrithmic).

    Try:
    <pre>=LINEST(YValues,LN(XValues))</pre>


    Where XVAlues and YValues are your X and Y ranges.

    Steve

  4. #4
    New Lounger
    Join Date
    Aug 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Logarithmic Trendline (Windows 2000 Pro)

    Please see attached workbook for example of table and chart.

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

    Re: Logarithmic Trendline (Windows 2000 Pro)

    Here is the formula for cell E9:

    =TREND($E$5:$E$8,LN(ROW(1:4)),LN(ROW()-4))

    See attached workbook. Your chart doesn't have x values defined, so I used the ROW function to provide the x values for the TREND function. You could also put the values 1 through 8 in B5:B12 and refer to these instead.

  6. #6
    New Lounger
    Join Date
    Aug 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ThanksRe: Logarithmic Trendline (Windows 2000 Pro)

    Thank you very much.

Posting Permissions

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