Problem creating a logarithmic trend line

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

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

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