Logarithmic Trendline

Ok, I apologize if this is a stupid question, but I'm really confused
about a result from Excel's trendline options.

My data is below:

X	Y
-0.15	-1.05
-0.14	-0.98
-0.13	-0.65
-0.12	-0.6
-0.11	-0.33
-0.10	-0.3
-0.09	-0.2475
-0.08	-0.22
-0.07	-0.1925
-0.06	-0.12
-0.05	-0.1
-0.04	-0.08
-0.03	-0.03
-0.02	-0.02
-0.01	-0.01
0.00	0
0.01	0.005
0.02	0.01
0.03	0.015
0.04	0.02
0.05	0.025
0.06	0.03
0.07	0.035
0.08	0.04
0.09	0.045
0.10	0.05
0.11	0.055
0.12	0.06
0.13	0.065
0.14	0.07
0.15	0.075

You can see from the data that the rate of change in the Y values
increases quickly as X increases, hence my use of a logarithmic
trendline.

Graphing this in excel, selecting Add Trendline > Logarithmic >
Options > Display equation on chart produces an excellent fit for the
data and the following equation for the fitted line:

y = 0.3359Ln(x) - 0.9859

But this doesn't make sense; trying to replicate the Y values based on
the equation will fail because you can't calculate Ln(x) if x is zero
or negative. Excel's help even specifically says that "a logarithmic
trendline can use both negative and positive values", but I don't
understand how this is possible.

I know that a transformation is normally required to model negative
data in a logarithmic setting, but why doesn't the displayed equation
describe the transformation?

Any help on this would be greatly appreciated.

0
3/28/2007 9:16:39 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
745 Views

Similar Articles

[PageSpeed] 56

On Wed, 28 Mar 2007, in microsoft.public.excel.charting,
jonathan.laberge@gmail.com said:
>Graphing this in excel, selecting Add Trendline > Logarithmic >
>Options > Display equation on chart produces an excellent fit for the
>data and the following equation for the fitted line:
>
>y = 0.3359Ln(x) - 0.9859
>
>But this doesn't make sense; trying to replicate the Y values based on
>the equation will fail because you can't calculate Ln(x) if x is zero
>or negative. Excel's help even specifically says that "a logarithmic
>trendline can use both negative and positive values", but I don't
>understand how this is possible.

You had me really puzzled for a second there, because when I tried it, 
the logarithmic trendline wasn't available to me at all! Which is as it 
should be, as I understand it. The program had correctly noted that the 
data was not capable of fitting to that or a number of other forms. Only 
linear, polynomial and moving average could be selected.

Then the penny dropped, I made the required change, and reproduced your 
fit equation perfectly: y = 0.33594Ln(x) - 0.98594

You've got a line chart type. The x axis is a category axis that just 
reproduces all the x values in a line of labels, but really the values 
the chart is using are 1,2,3,4, etc.  Naturally the log trend fit works, 
because the x values it sees are all positive. But the equation is not a 
good prediction of what y will be, given x, because it's not working 
from the correct values of x.

Change your chart type to Scatter (XY) Chart, and you'll find the log 
trend is no longer available.

-- 
Del Cotter
NB Personal replies to this post will send email to del@branta.demon.co.uk,
    which goes to a spam folder-- please send your email to del3 instead.
0
del1907 (586)
3/28/2007 10:18:29 PM
The reason for this 'odd' result is that you have make a Line chart when you 
needed an XY chart. In a Line chart the x-category values are treated as 
ordinals (1,2,3,4) no matter what is displayed. You are not the first to 
fall into this pit - Microsoft really needs a better name for Line charts 
('Category' chart would do)

When I make an XY chart of your data it is clearly not logarithmic. (Further 
proof of this: I transposed the values to get rid of negatives and still 
Excel does not offer a log trendline)
It might fit a logistics curve (Google to find meaning)

Depending on your need, I would be temped to use a fourth-order polynomial.

best wishes
-- 
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

<jonathan.laberge@gmail.com> wrote in message 
news:1175116599.926643.262120@o5g2000hsb.googlegroups.com...
> Ok, I apologize if this is a stupid question, but I'm really confused
> about a result from Excel's trendline options.
>
> My data is below:
>
> X Y
> -0.15 -1.05
> -0.14 -0.98
> -0.13 -0.65
> -0.12 -0.6
> -0.11 -0.33
> -0.10 -0.3
> -0.09 -0.2475
> -0.08 -0.22
> -0.07 -0.1925
> -0.06 -0.12
> -0.05 -0.1
> -0.04 -0.08
> -0.03 -0.03
> -0.02 -0.02
> -0.01 -0.01
> 0.00 0
> 0.01 0.005
> 0.02 0.01
> 0.03 0.015
> 0.04 0.02
> 0.05 0.025
> 0.06 0.03
> 0.07 0.035
> 0.08 0.04
> 0.09 0.045
> 0.10 0.05
> 0.11 0.055
> 0.12 0.06
> 0.13 0.065
> 0.14 0.07
> 0.15 0.075
>
> You can see from the data that the rate of change in the Y values
> increases quickly as X increases, hence my use of a logarithmic
> trendline.
>
> Graphing this in excel, selecting Add Trendline > Logarithmic >
> Options > Display equation on chart produces an excellent fit for the
> data and the following equation for the fitted line:
>
> y = 0.3359Ln(x) - 0.9859
>
> But this doesn't make sense; trying to replicate the Y values based on
> the equation will fail because you can't calculate Ln(x) if x is zero
> or negative. Excel's help even specifically says that "a logarithmic
> trendline can use both negative and positive values", but I don't
> understand how this is possible.
>
> I know that a transformation is normally required to model negative
> data in a logarithmic setting, but why doesn't the displayed equation
> describe the transformation?
>
> Any help on this would be greatly appreciated.
> 


0
bliengme5824 (3040)
3/28/2007 10:21:19 PM
Reply:

Similar Artilces:

Logarithmic charts
How should the data be entered in a logarithmic chart. I want the log data entries to be on the left and the other data to be displyed on the bottom. My chart will give a logarithmic resistance vs RH. I have tried to list the data by columns left to right first the resistance then thRH for 4 different series. It just doesn,t look right and I cannot change anything easily. Are there any sources for making charts in excel ,especialy Logarithmic ones? Thanks for any help? Hi Ed, In A1:A10 enter your resistance values (the x-values) In B2:B10 enter the RH (meaning?) values (the y-values) Sel...

How do I write a trendline constant into a cell?
Trendline equation constants. A trendline has been drawn on a chart and an acceptable equation has been obtained. The constants in this equation must be used in other formulas. How are these constants written into cells that can be used in formulas? This is needed when the data producing a trendline, can change and thus have an effect through the page. You can get this using the worksheet array function LINEST (see the help files), or by parsing the formula using Dave Braden's code, posted here: http://groups-beta.google.com/group/microsoft.public.excel.misc/browse_frm/thread/edb6...

no trendline option available
I have 3 sets of data for different conditions. When charted, 2 sets have the option to "add trendline", but the third set (with more "ups and downs' in the data) does not have the option available. There are no empty cells in the source data nor are there multiple series on the chart. Any ideas why I don't have the ability to add a trendline on this set of data. At first, I thought it was perhaps because there isn't a high enough order in the polynomial fit, but paring down the data so there is only one peak did not help (that is, add trendline was still not ...

logarithmic scale in excel?
how do i plot a graph with the x axis in a semi log format using excel 2007? i no how to do it in the previous version of excel but cant find out how to do it in this version...thanks for any help Right click on the axis to be treated (the numbers will then have a box around them) Select Format Axis from the popup menu to open the format dialog On the Axis Options tab (look to the left to see the tab names), about half-way down, locate Logarithmic Scale and check the box Note that we can now use logs to various bases not just 10; I like to use base 2 in many circumstances best wishes --...

0 on a logarithmic axis
I have a chart I am trying to plot data from 0ng to 1.9mg and a logarithmic scale works beautifully except that I have an important value at a 0ng (the start of my curve). Is there anyway to make the 0 value show up in a logarithmic scale? Thanks, -E No. Zero on a log scale would be at minus infinity. You could change the value from zero to something very small (10^-n g), but how small you make it will affect where it appears on the log scale; that's how logarithms work. -- David Biddulph "E" <E@discussions.microsoft.com> wrote in message news:D416CA9D-CD05-41...

Trendline on chart created from pivotable
I have created a bar graph from a pivot table that shows the number of events that occurred on specific dates, grouped by month. When I right click the series to add a trendline the 'Add Trendline' option is grayed out. Why is this? Excel 2003. The original data is in the form of a single column of data, each row contains only a single date. All dates are valid and there are no nulls in the data set. Pivot charts are not nearly as useful as regular charts, despite their pivot behavior. I almost always make a regular chart from pivot tables. Here's how: Select a blan...

logarithms
Excel provides EXP as the inverse of LN. Does it provide yhe inverse of LOG10 or do I have to manufacture my own? Yes, I'm afraid you'll have to manufacture your own. It's eally difficult <g> =10^A1 On Mon, 7 Mar 2005 19:35:06 -0800, "KG" <KG@discussions.microsoft.com> wrote: >Excel provides EXP as the inverse of LN. Does it provide yhe inverse of LOG10 >or do I have to manufacture my own? or just stick to natural logarithms. heehee "Myrna Larson" wrote: > Yes, I'm afraid you'll have to manufacture your own. It's e...

Rounding in Trendline Equation
An Excel logarithmic trendline attached to a data series has the following: Equation Y=533371Ln(x)-3E+06. R2 = 0.9533 (a good fit for the data) Final value (where the trendline crosses the right-hand (secondary) axis) = ~420,000 (the value Iā€™m ultimately seeking) I have created a new series with this equation to create the same curve with the following: Formula: =533371LN(1)-3000000 Final value produced with this formula: 195,673 (obviously not close to the Excel value ~420,000) Changing the ā€œ-3E+06ā€ value to 2,600,000 produces 414,873 ā€“...

Logarithms
Thanks both, it is good to know that I wasn't missing some obvious button on an obscure menu somewhere and that Excel really isn't able to do this task on its own. The question is why Microsoft haven't done anything about it because it has been a bone of contention for a number of years and it is a reason for many in the engineering and scientific community to write the programme off as not a serious charting tool. That's a shame because to my mind Excel is the strongest member of the Office family. Does Microsoft plan to do something about it? =========================...

Logarithmic Scales
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Ive tried and tried to find this... but i just cant seem to find any help with it. I have no trouble in changing my Y axis to a log scale... but i cant seem to find a way to add the log scale to the X axis. The 'scale' options that i see for the X-asis are just simple things like inverting the data adn the locations of the information on the graph. Can someone help me figure this out... In article <59bb656f.-1@webcrossing.JaKIaxP2ac0>, jeffus13@officeformac.com wrote: > Version: 2008 > Operating Sys...

Scope value In trendline chart incorrect
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hi, <br> I have a big problem with a chart I have created a simple chart with this values: <br><br>X Y <br> 1.13700&#09; 0.39213 <br> 1.46200&#09; 0.23851 <br> 1.63300&#09; 0.13124 <br> 1.87500&#09; 0.04913 <br><br>and I add a trendline , when I extract the formula the scope value and the interceptor value are wrong, If I try to calculate the same value with excel formule (SCOPE - INTERCEPTOR) I obtain a corre...

Logarithmic graph
Is there a way to have a logarithmic graph ignore the cells that contain a 0 value or no value in the graphed range?? Thanks ahead of time for the help. Log(0) is infinite. What do you propose instead? Logarithmic graphs do ignore cells that contain no value. Do you perhaps mean that it is not ignoring cells that contain zero length strings? Try using =IF(<condition>,<formula>,#N/A) instead of =IF(<condition>,<formula>,"") or =IF(<condition>,<formula>,0) Jerry John Babcock wrote: > Is there a way to have a logarithmic graph...

Logarithmic Chart
Does anyone know how to create a line chart that displays both the X AN Y axes in logarithmic format -- bobpanke ----------------------------------------------------------------------- bobpankey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1516 View this thread: http://www.excelforum.com/showthread.php?threadid=26799 Bob, Most charts are category charts. On the x axis are equally spaced categories (Apples, Oranges, etc.), and their associated values are plotted on the Y axis, optionally logarithmically. It sounds as though you need to plot points, with b...

Re: Logarithmic Trendlines in Excel
I have a data set, and when I choose to add a trendline, I can't select the logarithmic trendline option. Ive tried changing my initial value from 0 to 0.1 in my data set, but this only enables the exponential trendline option. The log trendline equation is y=a*ln(x)+b. Clearly it doesn't allow x <=0. OTOH, the exponential trendline equation is y=a*exp(b*x). It can be transformed into a linear form with ln(y)=ln(a)+b*x. It doesn't allow y<=0. You may also want to look at an article I wrote: Trendline coefficients http://www.tushar-mehta.com/excel/tips/trendline_coef...

logarithmic scales
Hi I have plotted a chart using a logarithmic scale - is it true to say that on a logarithmic scale the distance between the points are proportional - whereas on a linear scale the distance between the figures are relative?? Thanks In a word: NO -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email <miriamlight@gmail.com> wrote in message news:1157445149.864746.180940@p79g2000cwp.googlegroups.com... > Hi > > I have plotted a chart using a logarithmic scale - is it true to say > that on a logarithmic scale > the distance between the points are prop...

Logarithmic trendlines
Can someone please tell me how to display a log base 10 equation for a logarithmic trendline in an excel chart Multiply the coefficient of ln(x) by ln(10). Alternatively, get the coefficients in the worksheet by selecting a 5 row by 2 column range and entering the array formula =LINEST(range-with-y-values,LOG10(range-with-x-values),TRUE,TRUE). To enter an array formula complete data entry with the CTRL+SHIFT+ENTER combination rather than just the ENTER or TAB key. -- Tushar Mehta http://www.tushar-mehta.com Custom business solutions leveraging a multi-disciplinary approach In Excel 200...

Ploynomial Trendline Equation Changes After Copy Paste
In Excel 2007 if you create a XY Scatter Plot and then add a polynomial trendline to the plot, display the equation, then try and copy/paste the chart to a Word 2007 document the trendline equation in the chart drops one of the orders. If you right click and select Format Trendline, it still lists the correct order equation, but it does not show. If you delete the trendline, and then add it again in the pasted copy of the chart the problem is fixed. I have reproduced this using 2,3,4,5, and 6th order polynomial equations. I was unable to reproduce this behaviour. Can you give more ...

Trendline Question- Graph with TWO Y Axis???
I have a graph which uses a primary & a secondary Y axis. The trendline works fine for the series which uses the primary Y axis, but the series using the secondary Y axis adds a trendline based on the primary Y axis. Am I doing something wrong? thnx When you use the Add trend line dialog check that the trend line is based on the correct data series. phil6666 wrote: > I have a graph which uses a primary & a secondary Y axis. > > The trendline works fine for the series which uses the primary Y axis, > but the series using the secondary Y axis adds a trendline based o...

Summing trendlines in excel charts?
Hi Guys Is it possible to sum trendlines, or simply series, in excel charts? I have a large spreadsheet recording financial expenditure in respect of about 10 different job numbers, with each of these graphed on the chart showing trend lines. The problem is, if you simply chart the data in aggregate in order to find an overall trend line for expenditure, ie. set the source data to the various columns under each job number, there are large numbers of transactions under different job numbers on the same date, which means that these transactions aren't summed but appear as different points ...

In the x axis, logarithmic scale
As previously reported, I used scatter chart. However, my data are bellow 1 (0.1; 0.5) and my y axis is in the middle of the chart. How can I put Y axis on the left keeping these values? Thanks Charles - To change the location of the Y axis on an XY (Scatter) chart, select the horizontal X axis, choose Format Selected Axis | Scale, uncheck the box for "Value (Y) Axis Crosses At," and specify an X value. - Mike www.mikemiddleton.com "Charles" <Charles@discussions.microsoft.com> wrote in message news:9D9C9721-D0E9-439B-9A85-159BFA940254@microsoft.com... > ...

Logarithmic Trendline
Ok, I apologize if this is a stupid question, but I'm really confused about a result from Excel's trendline options. My data is below: X Y -0.15 -1.05 -0.14 -0.98 -0.13 -0.65 -0.12 -0.6 -0.11 -0.33 -0.10 -0.3 -0.09 -0.2475 -0.08 -0.22 -0.07 -0.1925 -0.06 -0.12 -0.05 -0.1 -0.04 -0.08 -0.03 -0.03 -0.02 -0.02 -0.01 -0.01 0.00 0 0.01 0.005 0.02 0.01 0.03 0.015 0.04 0.02 0.05 0.025 0.06 0.03 0.07 0.035 0.08 0.04 0.09 0.045 0.10 0.05 0.11 0.055 0.12 0.06 0.13 0.065 0.14 0.07 0.15 0.075 You can see from the data that the rate of change in the Y values increases quickly as X increases, henc...

Logarithmic scale
Hi, would any of you know of a site where i can find information regarding X and Y axis logarithmic scaling ? Thanx What kind of information? To use a log scaled axis, double click the axis and in the resulting dialog box, from the Scale tab select 'Log scale.' To use axis values other than powers of 10, see the Excel/Tutorials/Flexible Log Scale page of my web site. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <197b501c44d74$20458ab0$a601280a@phx.gbl>, euvaldojr@ig.com.b...

Logarithmic plotting
Hello I am wanting to do a Log Plot (both Axis) on the following data. How do I do this in Excel? Stage Discharge 0.291 0.873 1.237 11.6 0.7 5.16 0.295 0.889 0.288 0.783 0.288 0.811 0.288 0.771 0.48 2.53 0.284 0.737 0.333 1.12 3.261 44.8 0.378 1.53 0.389 1.63 0.444 1.978 0.325 0.96 0.306 0.932 0.301 0.855 0.82 6.87 0.834 7.26 0.877 6.57 1.045 9.18 1.764 18 3.121 39.9 4.297 68.9 4.526 77.6 3.085 30.5 0.087 0.061 With the data in A1:B28: First select all the data (and headers) and use Data|Sort to sort by Stage Now select A1:B28 and make an XY chart Double click x-axis, open Scale tab and...

Resolving Polynomial Trendline Formula for Chart
How would the following Chart equation be resolved in Excel: y = 147832x2 - 150195x + 2E+07 Thanks! Please explain what you mean by "be resolved" To get the trendline values into cells see http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "soccerkatie" <soccerkatie@discussions.microsoft.com> wrote in message news:FA5E6D19-13E3-40CB-8238-6B4AF88A26F2@microsoft.com... > How would the following Chart equation be resolved in Excel: > > y = 147832...

negative/zero values on logarithmic plot warning
Hello all, I realize that a negative or a zero plot on a logarithmic scale is meaningless. However, many times I'm working with data that is full of noise an non-ideal, i.e. it contains zeros and/or negative values. I still would like to plot them on a logarithmic scale. Excel does this quite well and ignores the negative/zero values on the chart. However, IT PRODUCES A WARNING EVERYTIME I TOUCH THE CHART! I make tons and tons of these graphs, so in order to save what little sanity I have remaining, I must find a way to turn this warning off. Please help me. Hi, Try using a...