Logarithmic scale + trendlines

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I found a serious bug/mistake in Excel Trendline addition: <br>
Given a linear correlation, plotted as XY (Scatter) spanned over several magnitudes. Adding a linear trendline works fine. However, changing the linear scale to logarithmic will not change the trendline to logarithmic, consequently showing the correlation in a completely wrong way. <br>
I checked it with previous Win version of Excel (2003) that works fine and changes the trendline according to scale. However, Excel 2007 (Win) has the same problem as 2008 Mac Excel. <br>
I was a very annoying problem I encountered with since I started the work with logarithmic scales and thus no good trendline fitting was possible. <br>
I also sent a request message to Microsoft for revision. <br><br>Anyone could confirm my observation or has a suggestion?
0
AVeg
12/16/2009 10:33:25 AM
mac.office.excel 1146 articles. 0 followers. Follow

6 Replies
2138 Views

Similar Articles

[PageSpeed] 25

In article <59baf262.-1@webcrossing.JaKIaxP2ac0>, AVeg@officeformac.com 
wrote:

> Version: 2008
> Operating System: Mac OS X 10.5 (Leopard)
> Processor: Intel
> 
> I found a serious bug/mistake in Excel Trendline addition: <br>
> Given a linear correlation, plotted as XY (Scatter) spanned over several 
> magnitudes. Adding a linear trendline works fine. However, changing the 
> linear scale to logarithmic will not change the trendline to logarithmic, 
> consequently showing the correlation in a completely wrong way. <br>
> I checked it with previous Win version of Excel (2003) that works fine and 
> changes the trendline according to scale. However, Excel 2007 (Win) has the 
> same problem as 2008 Mac Excel. <br>
> I was a very annoying problem I encountered with since I started the work 
> with logarithmic scales and thus no good trendline fitting was possible. <br>
> I also sent a request message to Microsoft for revision. <br><br>Anyone could 
> confirm my observation or has a suggestion?

Yeah - stop using Trendlines.   Entirely.

If you must use excel for advanced analysis like this, learn about 
LINEST and LOGEST.

-- 
Team EM to the rescue!    http://www.team-em.com
0
Carl
12/16/2009 10:27:37 PM
Hi Carl, <br><br>Thanks for the reply and suggestion.  I tested LINEST that works OK but the  curve drawn along the logarithmic scale is not continuous but broken. <br>
So, my final conclusion is NOT to use Excel anymore (as I do not have Office 2004) and stick to NUMBERS instead, which does the work excellently and more fun to work with. <br><br>Akos
0
AVeg
12/27/2009 5:08:01 PM
Hi Aveg, <br><br>I've been trying to reproduce the issue you reported. The steps I tried are: <br><br>1. Create a scatter chart <br>
2. Add a linear trendline <br>
3. Change the Y-axis to &quot;logarithmic scale&quot; <br>
4. The trendline remains linear <br><br>I followed the same steps on MacXL2004 and WinXL 2003 - both have the same result: the trendline remains linear. I was wondering whether my steps are correct. <br><br>Thanks,  <br>
XinXin Liu <br>
Macintosh Business Unit, Microsoft  <br><br>This posting is provided &quot;AS IS&quot; with no warranties, and confers no rights.  <br><br>> I found a serious bug/mistake in Excel Trendline addition: <br>
> Given a linear correlation, plotted as XY (Scatter) spanned over several magnitudes. Adding a linear trendline works fine. However, changing the linear scale to logarithmic will not change the trendline to logarithmic, consequently showing the correlation in a completely wrong way. <br>
> I checked it with previous Win version of Excel (2003) that works fine and changes the trendline according to scale. However, Excel 2007 (Win) has the same problem as 2008 Mac Excel. <br>
> I was a very annoying problem I encountered with since I started the work with logarithmic scales and thus no good trendline fitting was possible. <br>
> I also sent a request message to Microsoft for revision. <br>
>  <br>
> Anyone could confirm my observation or has a suggestion?
0
XinXin
12/29/2009 4:22:53 AM
Hi! <br><br>I am afraid I was not clear in my first post. I meant the X axes to be changed first of all. <br><br>I tested Excel 2008 Mac and 2007 Win with negative results, i.e., it does not change the linear trendlines into logarithmic when the X axes is changed to logarithmic. <br><br>I also tested Excel 2003 Win with positive results, i.e., it does change the trendlines to logarithmic. However, no other Office versions I could test. <br><br>Hopefully, this can help you. <br><br>Yet again, I stick to Numbers since then, which works fine. <br><br>Akos
0
AVeg
1/8/2010 2:05:55 PM
In article <59baf262.3@webcrossing.JaKIaxP2ac0>, AVeg@officeformac.com 
wrote:

> Hi! <br><br>I am afraid I was not clear in my first post. I meant the X axes 
> to be changed first of all. <br><br>I tested Excel 2008 Mac and 2007 Win with 
> negative results, i.e., it does not change the linear trendlines into 
> logarithmic when the X axes is changed to logarithmic. <br><br>I also tested 
> Excel 2003 Win with positive results, i.e., it does change the trendlines to 
> logarithmic. However, no other Office versions I could test. 
> <br><br>Hopefully, this can help you. <br><br>Yet again, I stick to Numbers 
> since then, which works fine. <br><br>Akos

As I said,   do NOT use Trendlines to get curve fits.   There are more 
problems than just the one you are having.   Read up on LINEST and 
LOGEST, and use these to get your fit coefficients in a worksheet.

-- 
Team EM to the rescue!    http://www.team-em.com
0
Carl
1/8/2010 5:58:35 PM
Hi AVeg, with the additional information, I can repro this bug. We are investigating it. <br><br>Thanks,  <br>
XinXin Liu  <br>
Macintosh Business Unit, Microsoft  <br><br>This posting is provided &quot;AS IS&quot; with no warranties, and confers no rights.  <br><br>> Hi! <br>
>  <br>
> I am afraid I was not clear in my first post. I meant the X axes to be changed first of all. <br>
>  <br>
> I tested Excel 2008 Mac and 2007 Win with negative results, i.e., it does not change the linear trendlines into logarithmic when the X axes is changed to logarithmic. <br>
>  <br>
> I also tested Excel 2003 Win with positive results, i.e., it does change the trendlines to logarithmic. However, no other Office versions I could test. <br>
>  <br>
> Hopefully, this can help you. <br>
>  <br>
> Yet again, I stick to Numbers since then, which works fine. <br>
>  <br>
> Akos
0
XinXin
1/11/2010 10:07:05 AM
Reply:

Similar Artilces:

Linear spacing of points in log scale
Hi, I am searching for a formula to derive linear spacing in a log chart. The spacing between 1 and 2 in log scale much more than between 8 and 9. To derive a 'smooth' graph I need more points at low values of the log scale. For a sufficient number of points for the x-axis i am searching for a formula to derive this. Ron, It's raising a number to arithmetically spaced powers that will result in such spacing on a log scale. Try this. Put a bunch of arithmetically spaced numbers in column A, and the following in B, copied down: =10^(A1/$C$1) Example: Column A contains 1,2,...

Scaling A Chart
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have a chart that is driven off a simple table that is filled in by the user. That table has up to 13 data points, but in most cases the table will only have 5 to 8 data points. If they have 8 the remaining data will be zero. I would like to chart to scale automatically to only have 8 points not all 13, so if I put a &quot;If&quot; formula in the series that plot the chart I can automatically control the amount of data point plotted. The series look like this: =SERIES('TREND ANALYSIS'!$B$24,'TRE...

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...

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 --...

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...

Exchange capacity and scaling
Hi, we are currently nearing 16g for our Exchange 2003 Standard databases and was wondering the following: - how many sets of database files can exchange standard support. - Can i create another storage group and thus mailbox and public folder store for load balancing - Can i do this on the same exchange server, providing enough resources - is this a better method of scaling exchange than implementing another server wtih another storage group Exchange standard can only have one mailbox database, although with sp2 the limit is now 75gb instead of 16. You will need to upgrade to the En...

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 ...

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...

Chart Axis Showing Lapse/Interupt Scale
I am using the columns with 2 lines chart and my data on the X Axis is number of events and time for the event. The Time for the event runs from 0 to 2500 minutes, but I only have one event that is greater than 2000 minutes. I'm trying to create a scale where I have 0, 500, 1000, then want to skip to 2000, 2500. Is there a way to change this scale so that the other event times such as 6, 247, 577 would appear and then the other 2200 time interval would be plotted. Not even sure I'm describing this correctly. I think I've seen this before and the indicator on the X axis...

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...

Is it possible to use XLST to set Excel worksheet margins, scaling?
Heres my problem: I am outputting XML from a dataset and using XSLT to format the output as HTML and the Response.ContentType = "application/vnd.ms-excel" directive to load the output as an Excel file running within the context of the browser. This works fine and the result is a nicely formatted Excel report. I was wondering if there was any way to also send additional formatting information like page margins and scaling. I've seen some examples of this using XMLSS but am not sure how to construct these types of commands in XSLT. Thanks for any help! Hello! > I am outputti...

Configuring the WEIGHTRONIX - 6720 Scale
We are using RMS 2.0 and are trying to configure the scale to be recognize by the RMS software. The scale seems to installed correctly on its own, but the interaction to read the weight inside the RMS, does not work. Here are the details : When we use the weight scale admin, it gives a corect weight. But after when we go the Microsoft RMS and selection the option to configure Microsoft opos service objects to configure the scale, it tells us weight - no data, status - error extended 114. Any thoughts on how to solve it with this particular scale ? Thanks. (5952) -- GCI ...

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? =========================...

Bug with gradient fills and max scale
I have a bar chart with gradient fills. The bar chart has a max value set so some bars are cut off. This chart is displayed in a picture link. In this picture the bars continue "through the roof" of the chart. Has anyone else encountered this? I can solve it by having solid fills, is there another way? My advice is to use solid fills. Not only is it simpler, but sometimes gradients can distract the reader or imply certain things about the data. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Marcus" <M...

The scale on vertical axis equals to scale on vertical axis of ano
Hello, I have a chart that displays 'before product implementation' scenario and another one next to it that dispalys 'after product implementation'. The user will have two countries to choose from so I have to leave the scale on auto rather than fix it because the the countries have very different size markets. What I would like to do is to make the vertical scale of graph 1 equal to the vertical scale of graph 2 so that I can show the impact of the product more clearly. Is there a way to do this which doesn't involved fixing the scales? Many thanks...

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...

Page scaling and page breaks
Using Excel 2007, when I set the page scaling to restrict the width of the document to one page only Excel enters a page break after EACH ROW and EACH COLUMN so a spreadsheet with 5 rows and fice colums ends up being 25 pages? When i go to page break view and try to reset ALL page breaks it will only reset the page breaks if the scaling is reset back to automatic. This has just started to happen - anyone have any ideas? It happens with all excel shhets, not just one so i think that there is some issue witrh the program. I am about to try and repair excel and possibly reinstall. Any bett...

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...

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 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...

I want to produce a log-log chart. Logrithmic scale on both axis.
You need to chart the data in an XY Scatter chart (not a Line chart). You can choose an XY Scatter chart subtype that connects the markers with lines. Then double click each axis, and on the Scale tab, check the Logarithmic Scale checkbox. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Thanks a lot. I'll try this. "transco" wrote: > ...