1 / 12

Model Using Trendline Non-Linear in Excel 2013

Model Using Trendline Non-Linear in Excel 2013. by Milo Schield Member: International Statistical Institute US Rep: International Statistical Literacy Project Director, W. M. Keck Statistical Literacy Project Slides at: www.StatLit.org/pdf /Model-Trendline-Multi-Excel2013-6up.pdf.

kendall
Download Presentation

Model Using Trendline Non-Linear in Excel 2013

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Model Using Trendline Non-Linear in Excel 2013 by Milo Schield Member: International Statistical Institute US Rep: International Statistical Literacy Project Director, W. M. Keck Statistical Literacy Project Slides at: www.StatLit.org/pdf /Model-Trendline-Multi-Excel2013-6up.pdf

  2. Goal: Summarize association between two variables Generate seven charts showing association between two quantitative variables (slides 5-11). Use seven different models: linear with forecast, linear with intercept = 0, polynomial, logarithmic, power, exponential and moving average. For each chart (except moving average), show trend-line, regression equation and R2. Show title and axis for all Review comparison of model R-squared on slide 12. No description of association (trend) is necessary. For details on using Trendline to build a model, see > www.StatLit.org/pdf/Model-Trendline-Linear-Excel2013-6up.pdf 2

  3. Using Chart TrendlineCreate Graph. Look for + Sign . 3 Select Chart Elements. Check Trendline box. Select More Options. Select Algebraic modelCheck Display Equation. Check Display R-squared.

  4. Algebraic Models 1) Linear: Y=a+bx. Straight line, simplest 2) Polynomial: Y= a+bx+cx2. Multiple curves 3) Logarithmic: Y=aLn(x)+b. Ratio scale. Equal ratios have equal differences Log10(1) = 0; Log10(10) = 1; Log10(100) = 2 4) Power model: Y=axb [Between log & exp.] 5) Exponential: Y=aex/b. Constant rate of change 6) Moving average: For time series 4

  5. Linear Model w Forecast 5

  6. Linear Model: Intercept = 0 6

  7. 2) Polynomial Model 7

  8. 3) Logarithmic Model 8

  9. 4) Power Model 9

  10. 5) Exponential Model 10

  11. 6) Moving Average Model 11

  12. Comparison of R-squared Percentage of Weight “explained by” Height 61.6% Linear model 40.6% Linear (intercept = 0) << Worst fit 62.1% Polynomial model 61.2% Logarithmic model 63.1% Power model 63.3% Exponential model << Best fit! Moving average model is not applicable here. 12

More Related