Logarithmic scale + trendlines

[AVeg]

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I found a serious bug/mistake in Excel Trendline addition:
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.
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.
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.
I also sent a request message to Microsoft for revision.

Anyone could confirm my observation or has a suggestion?

 

[Carl Witthoft]

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.

 

[AVeg]

Hi Carl,

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

Akos

 

[XinXin]

Hi Aveg,

I've been trying to reproduce the issue you reported. The steps I tried are:

1. Create a scatter chart
2. Add a linear trendline
3. Change the Y-axis to &quot;logarithmic scale&quot;
4. The trendline remains linear

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.

Thanks,
XinXin Liu
Macintosh Business Unit, Microsoft

This posting is provided &quot;AS IS&quot; with no warranties, and confers no rights.

 

[AVeg]

Hi!

I am afraid I was not clear in my first post. I meant the X axes to be changed first of all.

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.

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.

Hopefully, this can help you.

Yet again, I stick to Numbers since then, which works fine.

Akos

 

[Carl Witthoft]

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.

 

[XinXin]

Hi AVeg, with the additional information, I can repro this bug. We are investigating it.

Thanks,
XinXin Liu
Macintosh Business Unit, Microsoft

This posting is provided &quot;AS IS&quot; with no warranties, and confers no rights.