Linear trendlines with logarithmic scale

The trendline feature of Excel 2010 seems to malfunction. I have a Scatter chart with logarithmic scale on the horizontal axis and linear scale on the vertical axis. When I add a linear trendline, Excel draws a straight line, even though a linear function should look curved when plotted on a logarithmic scale. The trendline equation is correct, but the line that is drawn to the chart does not correspond to the equation. The same feature worked correctly in Excel 2003, but not with Excel 2010.
 
Question Info

Last updated June 11, 2018 Views 7,511 Applies to:

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

If you could provide an example I would be happy to report the problem to the Microsoft product team.  You could post code here to create the example, or email a completed example to me via the contact page of my website ( www.manville.org.uk )

Or you could report it to Microsoft support - they shouldn't charge for receiving a bug report.

Microsoft Excel MVP, Oxford, England. www.manville.org.uk

Did this solve your problem?

Sorry this didn't help.

I have sent an Excel file by email to Bill Manville, demonstrating the bug. It is easy: create a Scatter chart with three points, so that all the points are on the y=x line. Then add a linear trendline, and note that the fit is perfect: the line goes through all three points. Then change the horizontal axis to logarithmic scale, and things go wrong. The linear trendline still looks straight, and it does not go through the middle point. The linear trendline with a logarithmic axis should look curved and go through all the three points. Excel 2003 does this correctly but Excel 2010 has a bug.

 

Did this solve your problem?

Sorry this didn't help.

Thanks, Tuomo_823

I have received the file, confirmed your findings and posted it as a bug (ref. 668642) to the development team. 

The incorrect treatment also applies in 2007 and 2010 service pack 1 beta.

Don't expect an instant reaction (!), but hopefully it will be considered for a future 2010 service pack or the next full release.

Microsoft Excel MVP, Oxford, England. www.manville.org.uk

Did this solve your problem?

Sorry this didn't help.

Bill wrote:

Or you could report it to Microsoft support - they shouldn't charge for receiving a bug report.


Exactly how does "Joe user" do that, someone without a service contract with Microsoft?

Did this solve your problem?

Sorry this didn't help.

I would start by ringing my local Microsoft office.

I don't know what would happen thereafter.

But I understand the policy is not to charge for issues which turn out to be faults in the product.

Microsoft Excel MVP, Oxford, England. www.manville.org.uk

Did this solve your problem?

Sorry this didn't help.

Has this problem been fixed?

Did this solve your problem?

Sorry this didn't help.

Has this problem been fixed?


No, the problem has not been fixed.

 

Did this solve your problem?

Sorry this didn't help.

I have a bug that is exactly the opposite.  The trendline is drawn correctly, but the equation output is way off.  This is for the polynomial trendline.  I created a trendline that was a 6th order polynomial.  I then changed the format of the equation to include every number up to the first trailing zero (it took 20 decimal places).  I then wrote a function to test it using the exact same values I used to create the table and the values are horribly off.

Table:
         CenterFrequency    Multiplication Factor        Equation 
                  1.92                               1.793                       1.797027
                  2.05                               1.735                       1.744106
                  2.145                             1.697                       1.708377 
                  2.165                             1.688                       1.701163 
                  2.475                             1.58                          1.602624 
                  3.95                               1.25                           1.470034
                  4.7                                 1.146                         1.667236
                  6.175                             1                                 2.975724
                  6.725                             0.9582                       3.94604 
                  7                                     0.9392                       4.566414 
                7.437                                0.9112                      5.771059 
                7.75                                  0.8926                       6.821388 
                8.063                                0.8751                       8.049071 
                11.2                                  0.7425                      35.11282 
               12.45                                0.7043                       57.18633 
               12.825                              0.6939                      65.56833 
               12.975                              0.6899                      69.17648  


Center Frequency is the X coordinate and Multiplication Factor is the Y coordinate.  I plotted on an XY Scatter.  The following is the trend line equation:
y = 0.00000494814137885768x6 - 0.00025043715332770900x5 + 0.00518435711509937000x4 - 0.05672875326198410000x3 + 0.35527408802932800000x2 - 1.30907599153161000000x + 3.33183699495482000000

This is the excel VBA test function I wrote to calculate the third column:

Function FindMultiplicationFactor(f)
f6 = 4.94814137885768E-06 * (f ^ 6)
f5 = -2.50437153327709E-04 * (f ^ 5)
f4 = 5.18435711509937E-03 * (f ^ 4)
f3 = -5.67287532619841E-02 * (f ^ 3)
f2 = 0.355274088029328 * (f ^ 2)
f1 = -1.30907599153161 * f
f0 = 3.33183699495482
FindMultiplicationFactor = f4 + f3 + f2 + f1 + f0
End Function
'0.00000494814137885768x6 - 0.00025043715332770900x5 + 0.00518435711509937000x4 - '0.05672875326198410000x3 +
'0.35527408802932800000x2 - 1.30907599153161000000x + 3.33183699495482000000

As you can see, I copy pasted to avoid any fat finger errors from the commented section after the function.  Any help is appreciated.


Did this solve your problem?

Sorry this didn't help.

Nevermind.  I caught my problem in the function.  I was looking at this too long today.

Did this solve your problem?

Sorry this didn't help.

The trendline feature of Excel 2010 seems to malfunction. I have a Scatter chart with logarithmic scale on the horizontal axis and linear scale on the vertical axis. When I add a linear trendline, Excel draws a straight line, even though a linear function should look curved when plotted on a logarithmic scale. The trendline equation is correct, but the line that is drawn to the chart does not correspond to the equation. The same feature worked correctly in Excel 2003, but not with Excel 2010.

Three years later and this bug is still not fixed.

Nor is the bug which incorrectly calculates the R^2 of a trendline whose intercept is forced to zero.

Did this solve your problem?

Sorry this didn't help.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.