# Rounding in Trendline Equation

1. ## 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 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?

2. ## Re: Rounding in Trendline 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 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

3. ## Re: Rounding in Trendline Equation

Martin,

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
>

4. ## Re: Rounding in Trendline Equation

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" <PhilHageman@discussions.microsoft.com> wrote in message
news:C8280154-2B93-49AE-8C2B-4AECE76FEEE4@microsoft.com...
> 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
>
> "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
>>