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 Im 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?

Phil Hageman wrote:

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

Martin,

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

Thanks,
Phil

"Martin Brown" wrote:

Phil Hageman wrote:

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

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

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

"Phil Hageman" wrote in message
...
Martin,

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

Thanks,
Phil

"Martin Brown" wrote:

Phil Hageman wrote:

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