Home |
Search |
Today's Posts |
#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 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? |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Logarithmic Trendline Equation | Charts and Charting in Excel | |||
How do I write a trendline constant into a cell? | Charts and Charting in Excel | |||
R² expression for trendline in chart | Charts and Charting in Excel | |||
How do I get the trendline equation from Excel to script? | Charts and Charting in Excel | |||
Trendline error??? | Charts and Charting in Excel |