Problem creating a logarithmic trend line

Discussion in 'Microsoft Excel Charting' started by keith, Apr 8, 2009.

  1. keith

    keith Guest

    Hello,

    I created a simple series consisting of two columns.

    Column A contains the numbers 1, 2, 3, etc. through 50
    Column B contains the formula =log(A2), etc where the cell references A2,
    A3, A4,… A51 correspond to the numbers in column A. The numbers and headings
    are located in spreadsheet range A1:B51 I have pasted a comma delimited set
    of the actual numbers below.

    I then created two graphs. The first graph is a line graph of the series.
    It shows the log curve. The second graph is an XY(scatter chart) of the
    series. Both graphs show essentially, the same image: one is a line, the
    second is a series of dots.

    Using the “add trend line†option with the scatter chart, I should be able
    to add a trend line that traces the log curve. However, I see that adding a
    “logarithmic†trend line is not an option in Excel, or this logarithmic
    series. I wonder why this is so, and if there is some way to add a
    logarithmic trend line to a scatter chart. I have some data that is showing
    a logarithmic pattern and would like to see the trend line included.

    Following is a comma-delimited list of the numbers/formulas to be graphed.
    Just past the headings and numbers into a range beginning with cell A1, make
    sure the formulas are recognized as formulas, and you should be able to
    recreate the graphs.

    N,Log of N
    1,=log(A2)
    2,=log(A3)
    3,=log(A4)
    4,=log(A5)
    5,=log(A6)
    6,=log(A7)
    7,=log(A8)
    8,=log(A9)
    9,=log(A10)
    10,=log(A11)
    11,=log(A12)
    12,=log(A13)
    13,=log(A14)
    14,=log(A15)
    15,=log(A16)
    16,=log(A17)
    17,=log(A18)
    18,=log(A19)
    19,=log(A20)
    20,=log(A21)
    21,=log(A22)
    22,=log(A23)
    23,=log(A24)
    24,=log(A25)
    25,=log(A26)
    26,=log(A27)
    27,=log(A28)
    28,=log(A29)
    29,=log(A30)
    30,=log(A31)
    31,=log(A32)
    32,=log(A33)
    33,=log(A34)
    34,=log(A35)
    35,=log(A36)
    36,=log(A37)
    37,=log(A38)
    38,=log(A39)
    39,=log(A40)
    40,=log(A41)
    41,=log(A42)
    42,=log(A43)
    43,=log(A44)
    44,=log(A45)
    45,=log(A46)
    46,=log(A47)
    47,=log(A48)
    48,=log(A49)
    49,=log(A50)
    50,=log(A51)

    Thanks very much,

    Keith
     
    keith, Apr 8, 2009
    #1
    1. Advertisements

  2. keith

    Tushar Mehta Guest

    In both Excel 2003 and 2007 if I plot your data (column 1 as the x-values,
    column 2 as the y-values) in either a line chart or a xy scatter chart I can
    add a logarithmic trendline.

    The one time you will not be able to do so is if you have x-values that are
    < = 0. This is because the log trendline is of the form y = m*log(x) + c.

    Also, you should use the XY Scatter chart and not the line chart. The line
    chart treats the x values either as category values (i.e., they will all be
    equally spaced apart) or as dates, which have to be integers greater than
    zero. In your case, the results look identical since your x values are
    positive integers equally spaced but that need not be the case for your
    actual data. ;-)

    --
    Tushar Mehta
    http://www.tushar-mehta.com
    Custom business solutions leveraging a multi-disciplinary approach
    In Excel 2007 double-click to format may not work; right click and select
    from the menu


    "keith" wrote:

    > Hello,
    >
    > I created a simple series consisting of two columns.
    >
    > Column A contains the numbers 1, 2, 3, etc. through 50
    > Column B contains the formula =log(A2), etc where the cell references A2,
    > A3, A4,… A51 correspond to the numbers in column A. The numbers and headings
    > are located in spreadsheet range A1:B51 I have pasted a comma delimited set
    > of the actual numbers below.
    >
    > I then created two graphs. The first graph is a line graph of the series.
    > It shows the log curve. The second graph is an XY(scatter chart) of the
    > series. Both graphs show essentially, the same image: one is a line, the
    > second is a series of dots.
    >
    > Using the “add trend line†option with the scatter chart, I should be able
    > to add a trend line that traces the log curve. However, I see that adding a
    > “logarithmic†trend line is not an option in Excel, or this logarithmic
    > series. I wonder why this is so, and if there is some way to add a
    > logarithmic trend line to a scatter chart. I have some data that is showing
    > a logarithmic pattern and would like to see the trend line included.
    >
    > Following is a comma-delimited list of the numbers/formulas to be graphed.
    > Just past the headings and numbers into a range beginning with cell A1, make
    > sure the formulas are recognized as formulas, and you should be able to
    > recreate the graphs.
    >
    > N,Log of N
    > 1,=log(A2)
    > 2,=log(A3)
    > 3,=log(A4)
    > 4,=log(A5)
    > 5,=log(A6)
    > 6,=log(A7)
    > 7,=log(A8)
    > 8,=log(A9)
    > 9,=log(A10)
    > 10,=log(A11)
    > 11,=log(A12)
    > 12,=log(A13)
    > 13,=log(A14)
    > 14,=log(A15)
    > 15,=log(A16)
    > 16,=log(A17)
    > 17,=log(A18)
    > 18,=log(A19)
    > 19,=log(A20)
    > 20,=log(A21)
    > 21,=log(A22)
    > 22,=log(A23)
    > 23,=log(A24)
    > 24,=log(A25)
    > 25,=log(A26)
    > 26,=log(A27)
    > 27,=log(A28)
    > 28,=log(A29)
    > 29,=log(A30)
    > 30,=log(A31)
    > 31,=log(A32)
    > 32,=log(A33)
    > 33,=log(A34)
    > 34,=log(A35)
    > 35,=log(A36)
    > 36,=log(A37)
    > 37,=log(A38)
    > 38,=log(A39)
    > 39,=log(A40)
    > 40,=log(A41)
    > 41,=log(A42)
    > 42,=log(A43)
    > 43,=log(A44)
    > 44,=log(A45)
    > 45,=log(A46)
    > 46,=log(A47)
    > 47,=log(A48)
    > 48,=log(A49)
    > 49,=log(A50)
    > 50,=log(A51)
    >
    > Thanks very much,
    >
    > Keith
    >
     
    Tushar Mehta, Apr 8, 2009
    #2
    1. Advertisements

  3. keith

    keith Guest

    Thank you. That explains my problem. At one time when I was working with
    this, there were some negative values in the dataset.
    Thanks very much.
    Keith

    "Tushar Mehta" wrote:

    > In both Excel 2003 and 2007 if I plot your data (column 1 as the x-values,
    > column 2 as the y-values) in either a line chart or a xy scatter chart I can
    > add a logarithmic trendline.
    >
    > The one time you will not be able to do so is if you have x-values that are
    > < = 0. This is because the log trendline is of the form y = m*log(x) + c.
    >
    > Also, you should use the XY Scatter chart and not the line chart. The line
    > chart treats the x values either as category values (i.e., they will all be
    > equally spaced apart) or as dates, which have to be integers greater than
    > zero. In your case, the results look identical since your x values are
    > positive integers equally spaced but that need not be the case for your
    > actual data. ;-)
    >
    > --
    > Tushar Mehta
    > http://www.tushar-mehta.com
    > Custom business solutions leveraging a multi-disciplinary approach
    > In Excel 2007 double-click to format may not work; right click and select
    > from the menu
    >
    >
    > "keith" wrote:
    >
    > > Hello,
    > >
    > > I created a simple series consisting of two columns.
    > >
    > > Column A contains the numbers 1, 2, 3, etc. through 50
    > > Column B contains the formula =log(A2), etc where the cell references A2,
    > > A3, A4,… A51 correspond to the numbers in column A. The numbers and headings
    > > are located in spreadsheet range A1:B51 I have pasted a comma delimited set
    > > of the actual numbers below.
    > >
    > > I then created two graphs. The first graph is a line graph of the series.
    > > It shows the log curve. The second graph is an XY(scatter chart) of the
    > > series. Both graphs show essentially, the same image: one is a line, the
    > > second is a series of dots.
    > >
    > > Using the “add trend line†option with the scatter chart, I should be able
    > > to add a trend line that traces the log curve. However, I see that adding a
    > > “logarithmic†trend line is not an option in Excel, or this logarithmic
    > > series. I wonder why this is so, and if there is some way to add a
    > > logarithmic trend line to a scatter chart. I have some data that is showing
    > > a logarithmic pattern and would like to see the trend line included.
    > >
    > > Following is a comma-delimited list of the numbers/formulas to be graphed.
    > > Just past the headings and numbers into a range beginning with cell A1, make
    > > sure the formulas are recognized as formulas, and you should be able to
    > > recreate the graphs.
    > >
    > > N,Log of N
    > > 1,=log(A2)
    > > 2,=log(A3)
    > > 3,=log(A4)
    > > 4,=log(A5)
    > > 5,=log(A6)
    > > 6,=log(A7)
    > > 7,=log(A8)
    > > 8,=log(A9)
    > > 9,=log(A10)
    > > 10,=log(A11)
    > > 11,=log(A12)
    > > 12,=log(A13)
    > > 13,=log(A14)
    > > 14,=log(A15)
    > > 15,=log(A16)
    > > 16,=log(A17)
    > > 17,=log(A18)
    > > 18,=log(A19)
    > > 19,=log(A20)
    > > 20,=log(A21)
    > > 21,=log(A22)
    > > 22,=log(A23)
    > > 23,=log(A24)
    > > 24,=log(A25)
    > > 25,=log(A26)
    > > 26,=log(A27)
    > > 27,=log(A28)
    > > 28,=log(A29)
    > > 29,=log(A30)
    > > 30,=log(A31)
    > > 31,=log(A32)
    > > 32,=log(A33)
    > > 33,=log(A34)
    > > 34,=log(A35)
    > > 35,=log(A36)
    > > 36,=log(A37)
    > > 37,=log(A38)
    > > 38,=log(A39)
    > > 39,=log(A40)
    > > 40,=log(A41)
    > > 41,=log(A42)
    > > 42,=log(A43)
    > > 43,=log(A44)
    > > 44,=log(A45)
    > > 45,=log(A46)
    > > 46,=log(A47)
    > > 47,=log(A48)
    > > 48,=log(A49)
    > > 49,=log(A50)
    > > 50,=log(A51)
    > >
    > > Thanks very much,
    > >
    > > Keith
    > >
     
    keith, Apr 8, 2009
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Graphing a logarithmic regression line

    Guest, Nov 18, 2005, in forum: Microsoft Excel Charting
    Replies:
    3
    Views:
    973
    Guest
    Nov 18, 2005
  2. Guest
    Replies:
    1
    Views:
    327
    Bernard Liengme
    Oct 28, 2006
  3. FlexoC

    How do I set a Trend Line and Remove the Data Line

    FlexoC, Aug 22, 2008, in forum: Microsoft Excel Charting
    Replies:
    2
    Views:
    1,035
    FlexoC
    Aug 22, 2008
  4. Kamehameha

    Logarithmic Trend Line Formula

    Kamehameha, Mar 26, 2010, in forum: Microsoft Excel Charting
    Replies:
    0
    Views:
    933
    Kamehameha
    Mar 26, 2010
  5. Onion Knight

    Creating a linear trend line in Excel

    Onion Knight, Jun 15, 2012, in forum: Microsoft Excel Charting
    Replies:
    0
    Views:
    579
    Onion Knight
    Jun 15, 2012
Loading...

Share This Page