Logarithmic scale + trendlines
AV...@officeformac.com
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
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
AV...@officeformac.com
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
Xin...@officeformac.com
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 "logarithmic scale"
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 "AS IS" with no warranties, and confers no rights.
AV...@officeformac.com
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
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.
Xin...@officeformac.com
Thanks,
XinXin Liu
Macintosh Business Unit, Microsoft
This posting is provided "AS IS" with no warranties, and confers no rights.
> I am afraid I was not clear in my first post. I meant the X axes to be changed first of all.
>
>
>
>
>