+ Reply to Thread
Results 1 to 4 of 4

Rounding in Trendline Equation

  1. #1
    Phil Hageman
    Guest

    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. #2
    Martin Brown
    Guest

    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. #3
    Phil Hageman
    Guest

    Re: Rounding in Trendline Equation

    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
    >


  4. #4
    Michael R Middleton
    Guest

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




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts