PC Review


Reply
Thread Tools Rate Thread

Problem creating a logarithmic trend line

 
 
keith
Guest
Posts: n/a
 
      8th Apr 2009
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

 
Reply With Quote
 
 
 
 
Tushar Mehta
Guest
Posts: n/a
 
      8th Apr 2009
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
>

 
Reply With Quote
 
 
 
 
keith
Guest
Posts: n/a
 
      8th Apr 2009
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
> >

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Logarithmic Trend Line Formula Kamehameha Microsoft Excel Charting 0 26th Mar 2010 04:47 PM
How do I set a Trend Line and Remove the Data Line FlexoC Microsoft Excel Charting 2 22nd Aug 2008 05:15 PM
How do I specify x-axis data for a logarithmic line graph? =?Utf-8?B?a2N1bGVyaWU=?= Microsoft Excel Charting 1 28th Oct 2006 09:57 PM
Graphing a logarithmic regression line =?Utf-8?B?U3VzaWVidXNpZQ==?= Microsoft Excel Charting 3 18th Nov 2005 08:26 PM
creating logarithmic trendline equation function mparker Microsoft Excel Programming 2 17th Feb 2004 06:16 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 04:05 AM.