# 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?  Register To Reply

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  Register To Reply

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
>  Register To Reply

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
>>  Register To Reply