Rounding in Trendline Equation

[Guest]

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 – which very
closely approximates the value of the Excel trendline where it crosses the
right-hand axis.

Conclusion: Excel is rounding the 2,600,000 number up to 3 in the “-3E+06â€
factor of the equation.

Questions:
Is there a way to tell Excel not to round up to 3, but rather
use/display the exact number, something around 2,600,000, in place of
“-3E+06�
Or, is there another way to find the exact “-3E+06†number Excel uses
when it creates the equation?

 

[Martin Brown]

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 – which very
closely approximates the value of the Excel trendline where it crosses the
right-hand axis.

Conclusion: Excel is rounding the 2,600,000 number up to 3 in the “-3E+06â€
factor of the equation.

Questions:
Is there a way to tell Excel not to round up to 3, but rather
use/display the exact number, something around 2,600,000, in place of
“-3E+06�
Or, is there another way to find the exact “-3E+06†number Excel uses
when it creates the equation?

Set the numeric display format for the equation to have 16 decimal
digits and you will get the exact fitted parameters.

Regards,
Martin Brown

 

[Guest]

Martin,

Thanks for your reply. I've looked at Tool>Options, and Format Trendline
and can't find where to make the change your recomment. How do I make this
change?

Thanks,
Phil

 

[Michael R Middleton]

Phil -

The trendline equation is displayed in a text box on the chart. Select the
text box, and on the Formatting toolbar repeatedly click the Increase
Decimal button.

- Mike

www.mikemiddleton.com

++++++++++++++++++++++++++++++